CODEWITHSUNDEEP
phpmysql
theglove
theglove

Reputation: 45

Display values that are in Table2 but NOT in Table1

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?

Upvotes: 1

Views: 308

Answers (2)

Icarus
Icarus

Reputation: 63956

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

Upvotes: 0

Phil
Phil

Reputation: 164767

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.

Upvotes: 4

Related Questions