MySQL not equal

mrpeek

Hiya,
Need a little bit of help hoping someone here can help me out.

I have 2 tables within a database, lets call them table1 and table2. Each table has 1 column(columnA). What I would like to do is be able to tell what rows are different in each table. When I run an equal statement it works perfectly, but when I run a not equal statement, it will tell me every row in table1 that is not equal to table2 but what I want is just a list of all the numbers that are totally different. Is this possible?? Thanks for the help

mrpeek

to clarify a little bit more on that..
lets say I have
TABLE1
1
2
3
4
and
TABLE2
1
2
4
5

I want to be able to do a not equal statement and have it show only 3 and 5

dagon

it table a is
1
2
3
and
b is
1
3
2

what output do you expect ?

mrpeek

dagon;10993953 wrote:
it table a is
1
2
3
and
b is
1
3
2

what output do you expect ?

then I would expect nothing as an output

dagon

SELECT table_b.* FROM table_b
LEFT JOIN table_a ON table_b.id = table_a.id
WHERE table_a.item_id IS NULL

Derokorian

(SELECT column
   FROM table1
   WHERE column NOT IN (SELECT column FROM table2)
) UNION
( SELECT column
   FROM table2
   WHERE column NOT IN (SELECT column FROM table1)
)

mrpeek

dagon;10993956 wrote:
SELECT table_b.* FROM table_b
LEFT JOIN table_a ON table_b.id = table_a.id
WHERE table_a.item_id IS NULL

tried that and the output is null

SELECT table_b.*
FROM table_b
LEFT JOIN table_a ON table_b.phone = table_a.phone
WHERE
table_a.phone IS NULL

its just been one of those days, so I know its something super simple

dagon

INSERT INTO table_a (id) VALUES
(1),
(2),
(3);

INSERT INTO table_b (id) VALUES
(1),
(2),
(7);

SELECT table_b . *
FROM table_b
LEFT JOIN table_a ON table_b.id = table_a.id
WHERE table_a.id IS NULL

returned value =7

change b to 1,3,2

returned value =nothing

mrpeek

dagon;10993965 wrote:
INSERT INTO table_a (id) VALUES
(1),
(2),
(3);

INSERT INTO table_b (id) VALUES
(1),
(2),
(7);

SELECT table_b . *
FROM table_b
LEFT JOIN table_a ON table_b.id = table_a.id
WHERE table_a.id IS NULL

returned value =7

change b to 1,3,2

returned value =nothing

thanks for that.. I see what I was doing wrong.. just one of them days, swear today was monday..

Thanks for your help

Derokorian

CREATE TABLE `table1` ( `column` INT(5) );
INSERT INTO `table1` (`column`) VALUES (1),(2),(4),(5),(8);
CREATE TABLE `table2` ( `column` INT(5) );
INSERT INTO `table2` (`column`) VALUES (1),(3),(5),(7);

(SELECT `column`
   FROM `table1`
   WHERE `column` NOT IN (
      SELECT `column` FROM `table2`
   )
) 
   UNION 
(SELECT `column`
   FROM `table2`
   WHERE `column` NOT IN (
      SELECT `column` FROM `table1`
   )
)

Expected result: 2, 3, 4, 7, 8
Obtained result: 2, 3, 4, 7, 8

bradgrafelman

Derokorian;10993969 wrote:

CREATE TABLE `table1` ( `column` INT(5) );
INSERT INTO `table1` (`column`) VALUES (1),(2),(4),(5),(8);
CREATE TABLE `table2` ( `column` INT(5) );
INSERT INTO `table2` (`column`) VALUES (1),(3),(5),(7);

(SELECT `column`
   FROM `table1`
   WHERE `column` NOT IN (
      SELECT `column` FROM `table2`
   )
) 
   UNION 
(SELECT `column`
   FROM `table2`
   WHERE `column` NOT IN (
      SELECT `column` FROM `table1`
   )
)

Expected result: 2, 3, 4, 7, 8
Obtained result: 2, 3, 4, 7, 8

Okay, but now show us a benchmark of your 2 queries + 2 subqueries UNION'd together verses a simple JOIN as illustrated above.

Derokorian

Ok as soon as the simple join gives the unique numbers from both lists instead of just the one.

bradgrafelman

Derokorian;10993972 wrote:
Ok as soon as the simple join gives the unique numbers from both lists instead of just the one.

But why? That's not what the OP is looking for...

Derokorian

bradgrafelman;10993973 wrote:
But why? That's not what the OP is looking for...

Oh really? His second post could have fooled me.

mrpeek;10993952 wrote:
to clarify a little bit more on that..
lets say I have
TABLE1
1
2
3
4
and
TABLE2
1
2
4
5

I want to be able to do a not equal statement and have it show only 3 and 5

bradgrafelman

Oops, I see the problem now...

EDIT2: Aw crap, I forgot - MySQL doesn't support 'FULL OUTER JOIN'. I guess that's what you get for using MySQL. :p

EDIT3: Might as well throw out the MySQL workaround for the lack of FULL OJ's for completeness:

(
    SELECT table_a.colName 
    FROM table_a
    LEFT JOIN table_b ON table_a.colName = table_b.colName
    WHERE table_b.colName IS NULL
)
UNION
(
    SELECT table_b.colName
    FROM table_b
    LEFT JOIN table_a ON table_b.colName = table_a.colName
    WHERE table_a.colName IS NULL
)

Derokorian

Holy edits b-man! By the time I switched your first to work with my tables, and got back here to tell you that it was missing some values (I think because of the lack of FULL OUTER) and you have multiple edits and a completely different query! Also coalesce O_o something completely new! I've never even heard of it before...

Edit: Yes I RTFM and saw that it returns the first non-null value, or null if none exists.

bradgrafelman

I blame my lack of a nearby MySQL server... I think I edited that last post a solid 10 times or so. :/

Regardless, you're right - a simple LEFT JOIN won't give the items missing from the left table that are present in the right... unless you do two LEFT JOINs (switching the order of the tables in the second) and UNION the results together (a.k.a. "full outer join").

mrpeek

ok so let me change it up a bit.

so right now I have it working so it only shows me the difference between the tables...
how would I do it so it would only show me the same between tables

Table_a
1
2
4
5
Table_b
2
3
4
5
6

so i would want it to show me
2
4
5

bradgrafelman

Do an INNER JOIN between the two tables.