Display values that are in Table2 but NOT in Table1

Question

I have a mysql problem. Let's say for example I have two tables with the following values:

My question is how can I make a query that will only choose/display the values:

 kiwi, mango, strawberry

Can anyone provide a sample code for this?

score 4 · Answer 1 · answered Oct 26 '11 at 01:24

4

Option #1

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

Option #2

SELECT table1.fruit FROM table1
WHERE NOT EXISTS (
    SELECT 1 FROM table2
    WHERE table2.fruit = table1.fruit
)

I'd have to see the explain plans to recall which is more efficient but it really depends on any indexes you have created.

answered Oct 26 '11 at 01:24

Phil

Hi thanks for your answer. But what is the purpose of 'LEFT JOIN'? Thanks – theglove Oct 26 '11 at 01:25
@theglove Plenty of documentation available for that one. Try http://en.wikipedia.org/wiki/Join_(SQL)#Left_outer_join – Phil Oct 26 '11 at 01:27
What if the column name was not the same with the other but the column has the same value. for example table.abc an table.zxc they have different table name but some of their values match. Is it possible to incorporate? – theglove Oct 26 '11 at 06:39
@theglove of course, you just use the appropriate column names – Phil Oct 26 '11 at 07:04
In your option 1 how will you fetch the values from your query? – theglove Oct 26 '11 at 07:19
@theglove in PHP? Probably `PDOStatement:: fetch()` – Phil Oct 26 '11 at 07:28

score 0 · Accepted Answer · answered Oct 26 '11 at 01:24

0

select column from table1 where column not in (select column from table2)

answered Oct 26 '11 at 01:24

Icarus

Can be *extremely* inefficient for a large set in `table2` – Phil Oct 26 '11 at 01:26
@Phil Even if `column` is the index on the table? – Icarus Oct 26 '11 at 01:28
Thanks, then, how would you fetch/display the values of the query? – theglove Oct 26 '11 at 01:28
@Icarus See http://stackoverflow.com/questions/2065329/sql-server-in-vs-exists-performance and http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx for other reasons *not* to use `in()` – Phil Oct 26 '11 at 01:31
@Phil: That's a post related to SQL Server. This is MySQL. Aaron Bertrand mentions on that post that SQL Server 2008 transforms `IN` to `EXISTS` automatically. Your comment may be true but only on certain cases. It really depends on the optimizer implementation. – Icarus Oct 26 '11 at 01:37

2 Answers2