-1

I have the following table schema:

Table 1
-
field1
Table 2
-
field1 | field2

What I want to do is select field2 from the second table where field1 in the second table doesn't exist in the first table (field1).

I had this :

SELECT t2.field2
     , t2.field1
FROM table1 t1
   , table2 t2
WHERE t2.field1 != t1.field1

The problem is that this query will retrieve multiple repeated information from table2 if multiple rows apply in table1. I added DISTINCT and/or LIMIT but it still doesn't work. Any idea on how to do this?

potashin
  • 44,205
  • 11
  • 83
  • 107
Andre Calenta
  • 77
  • 2
  • 7

3 Answers3

3

You can use a subquery:

SELECT t2.field2, t2.field1 FROM table2 t2 WHERE t2.field1 NOT IN (SELECT t1.field1 FROM table1 t1);

This will give you all rows from table2 that have a field1 which is not in table1.

You can now use DISTINCT or LIMIT on the outermost query for any further processing.

Simeon Visser
  • 118,920
  • 18
  • 185
  • 180
2

You can use LEFT JOIN together with IS NULL check :

SELECT DISTINCT t2.field2
FROM table2 t2
LEFT JOIN table1 t1 ON t1.field1 = t2.field1
WHERE t1.field1 IS NULL
potashin
  • 44,205
  • 11
  • 83
  • 107
  • @GolezTrol : using `LEFT JOIN` appears to be a basic anti-join solution and the above one should be even [faster](http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/) than yours (_for MySQL RDBMS_). – potashin Jan 24 '15 at 23:31
  • I was reading that too. I don't know if it still applies though (the text is from 2009). But anyway, you've addressed my other remarks, so now this solution is at least a valid query too. And indeed possibly faster. – GolezTrol Jan 24 '15 at 23:45
  • @GolezTrol : Well, thank you for pointing out about the `DISTINCT` part – I've started answering after I've read the first part.) – potashin Jan 24 '15 at 23:48
  • By the way, that link is about `not nullable columns`. If the columns are nullable, you will need a slightly different query for joins and `not in`. The syntax for `not exists` remains the same (which is a big plus, I think) and it [performs better](http://explainextended.com/2010/05/27/left-join-is-null-vs-not-in-vs-not-exists-nullable-columns/) in that case. – GolezTrol Jan 24 '15 at 23:58
2

The title of your question is almost the command you need: 'NOT EXIST'. SQL is so simple. ;)

SELECT DISTINCT t2.field2 
FROM Table2 t2
WHERE NOT EXISTS (SELECT * FROM Table1 WHERE t1.field1 = t2.field1)
GolezTrol
  • 114,394
  • 18
  • 182
  • 210
  • how does this differentiate from NOT IN? – Andre Calenta Jan 24 '15 at 23:27
  • You can use `DISTINCT` now. It should work if you only select the field that you need. – GolezTrol Jan 24 '15 at 23:34
  • Not because of that... table 1 has multiple rows where it might not exist, so it returns multiple rows... – Andre Calenta Jan 24 '15 at 23:38
  • I reverted the table names. They now seem to match your question a bit better. ;) – GolezTrol Jan 24 '15 at 23:48
  • When compared to `not in`, this solution will still work if Table1.field1 is nullable and contains `null`s. I think `not in` will fail in that case. On the other hand, `not in` [used to be slightly faster](http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/), although I don't know if that is still the case, and it might depend on [the exact use case](http://dev.mysql.com/doc/refman/5.5/en/subquery-optimization-with-exists.html). – GolezTrol Jan 24 '15 at 23:54