0

I have two tables:

table1 
#id_table1 | code1
#---------------------
#   1      |  abc
#   2      |  abcd
#   3      |  abcde


table2
#id_table2|code2
#--------------------
#   1      |  aaa
#   2      |  bbb
#   3      |  abcde

If i want to join this two tables and get records which are in both tables:

SELECT table1.code1, table2.code2 FROM table1, table2
WHERE table1.code1=table2.code2

Result: abcde

It's easy, but now I need to do the opposite : I want records from table1.code1 which aren't in table2.code2

Result i need: abc, abcd

And records from table2.code2, which aren't in table1.code1

Result i need: aaa, bbb

I would appriciate any help - thanks in advance!

belialek
  • 79
  • 1
  • 1
  • 10
  • I don't think the duplicate that this was closed for matches this use case where you are trying to emulate FULL OUTER JOIN in MySQL. Now there are similar answer for that, but I don't think this question has the level of assumption that the reader already understands what a FULL OUTER JOIN is. – Mike Brant Jul 02 '15 at 19:41

2 Answers2

0

Actually just noticed this is tagged specifically for MySQL, which doesn;t support FULL OUTER JOIN (if you are on another SQL system that supports this, you can skip on down for preferred approach.

So, in MySQL you need to UNION together both a left and right join like this:

SELECT
  table1.code1,
  table2.code2
FROM table1
LEFT JOIN table2 ON table1.code1=table2.code2
WHERE table2.code2 IS NULL
UNION
SELECT
  table1.code1,
  table2.code2
FROM table1
RIGHT JOIN table2 ON table1.code1=table2.code2
WHERE table1.code1 IS NULL

If you have FULL OUTER JOIN compatibility, you would perform the FULL OUTER JOIN and look for cases where the join results in null records on the field you are trying to join on.

SELECT
  table1.code1,
  table2.code2
FROM table1
FULL OUTER JOIN table2 ON table1.code1=table2.code2
WHERE table1.code1 IS NULL OR table2.code2 IS NULL

Here is a well-known article explaining how to perform different types of joins: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

Mike Brant
  • 70,514
  • 10
  • 99
  • 103
0

Quite simple actually:

SELECT code1
FROM table1 LEFT JOIN table2
  ON table1.code1 = table2.code2
WHERE code2 IS NULL

And the same with the opposite table

Amit
  • 45,440
  • 9
  • 78
  • 110
  • This would only work in one direction and would not identify rows that exist in table2 but not in table 1. – Mike Brant Jul 02 '15 at 19:28
  • @MikeBrant the request was to get 2 separate result sets, and I noted the same query should be replicated and swapped for the other table (Your solution on the other hand, returns a single result set) – Amit Jul 02 '15 at 19:29
  • He didn't explicitly say he wanted two result sets, but I can definitely see how you could interpret the question that way. Either way, he should be able to get what he wants between our two answers :) – Mike Brant Jul 02 '15 at 19:33