1

Possible Duplicate:
MySQL - How to get a list of values in Table A that don’t exist in Table B?

I have three tables .

Table x:

+------+
| ID   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
+------+

Table Y

+------+
| ID   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

Table Z

+------+
| ID   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
|    7 |
+------+

I have to find the value exists in TABLE Z but it not exists in table X and Y .

Output:

+----+
| ID |
------
| 6  |
| 7  |
+----+

How can I get this?

Community
  • 1
  • 1
kannanrbk
  • 6,964
  • 13
  • 53
  • 94

4 Answers4

1
SELECT z.ID 
FROM TableZ z
LEFT JOIN TableX x on x.ID = z.ID
LEFT JOIN TableY y on y.Id = z.ID
WHERE x.Id IS NULL 
AND y.ID IS NULL

sqlFiddle(with Xavi Lopez and mine, both giving same result)

Raphaël Althaus
  • 59,727
  • 6
  • 96
  • 122
1

You could use EXISTS to make a subquery.

SELECT tz.id 
FROM Z tz 
WHERE 
    NOT EXISTS (select 1 from X tx where tx.id = tz.id)
    AND NOT EXISTS (select 1 from Y ty where ty.id = tz.id)

@RaphaëlAlthaus's answer is probably better on efficiency, though. See Can I get better performance using a JOIN or using EXISTS?

The MySQL reference has a section on EXISTS: 13.2.9.6. Subqueries with EXISTS or NOT EXISTS.

Community
  • 1
  • 1
Xavi López
  • 27,550
  • 11
  • 97
  • 161
  • 2
    Hmmm, not sure about efficency, but I must admit I usually prefer the "EXISTS" syntax for its better readability int these cases ;) – Raphaël Althaus Jan 28 '13 at 13:54
  • People. Did I miss the memo? What's wrong with LEFT JOIN? – Strawberry Jan 28 '13 at 14:12
  • 1
    @Strawberry: You missed it (tagline: readability). I admit it's a matter of personal preference though. – ypercubeᵀᴹ Jan 28 '13 at 14:34
  • @Downvoter: Can you please explain in which way is this answer not useful, so it can improved? – Xavi López Jan 28 '13 at 14:48
  • @Strawberry there's indeed nothing wrong with `left join`. As mentioned in the answer and related question, it _might_ be even better regarding efficiency. As for readability, `exists` might be easier for the OP to understand. And in this case it's not necessary to fetch any of the joined tables' data. – Xavi López Jan 28 '13 at 14:54
0
SELECT  a.ID
FROM    tableZ a
        LEFT JOIN
        (
            SELECT ID FROM tableX
            UNION 
            SELECT ID FROM tableY
        ) b ON a.ID = b.ID
WHERE   b.ID IS NULL
John Woo
  • 258,903
  • 69
  • 498
  • 492
0

you can use minus function

SELECT * FROM Z
MINUS
(
SELECT * FROM Y
UNION
SELECT * FROM X
)
pribina
  • 26
  • 2