MYSQL unselect query from second table

Question

Hi I have this two table

table 1

id    Selection  
-------------------
1     John           
2     Ely               
3     Marcus            
4     Steve           
5     Fritz           
6     Orly           
7     Carlo              
8     Lee

table 2

id    Selected 
-------------------
1     John                         
3     Marcus 
4     Steve                     
5     Fritz           
7     Carlo

the return would be the unselected rows. What would be the query for this output

id    Selection 
-------------------         
2     Ely                         
6     Orly                  
8     Lee

This pattern is referred to as an "anti-join". Usually the most efficient way to get this result is a LEFT JOIN operation, and then eliminate any rows that matched. What you are left with is the unmatched rows. — spencer7593, Jul 18 '13 at 07:09

score 2 · Accepted Answer · answered Jul 18 '13 at 06:57

2

Use LEFT JOIN to join both table and t2.ID IS NULL to remove common records

SELECT t1.* FROM table1 t1 
  LEFT JOIN table2 t2 
    ON t1.ID = t2.ID 
 WHERE t2.ID IS NULL

Output:

╔════╦═══════════╗
║ ID ║ SELECTION ║
╠════╬═══════════╣
║  2 ║ Ely       ║
║  6 ║ Orly      ║
║  8 ║ Lee       ║
╚════╩═══════════╝

See this SQLFiddle

answered Jul 18 '13 at 06:57

Himanshu

31,810
31
111
133

2

+1. This is the familiar anti-join pattern. Very nice. @Markie Mark: And beyond hoping it would work, you might try to understand how it works, and be able to confirm that it does work. – spencer7593 Jul 18 '13 at 07:14
1

it works.. my bad I switch the table joins.. thanks for this very bery helpful – Markie Mark Jul 18 '13 at 07:42
@MarkieMark - Glad! For more details related to how joins works see: [A Visual Explanation of SQL Joins](http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html) – Himanshu Jul 18 '13 at 07:46

AllTooSir · Answer 2 · 2013-07-18T07:03:37.213

1

You can use a Left Join:

 Select t1.id,t2.selection from 
 table1 t1 left join table2 t2 
 ON t1.ID = t2.ID 
 where t2.id is null;

edited Jul 18 '13 at 07:03

answered Jul 18 '13 at 06:58

AllTooSir

48,828
16
130
164

You just changed the whole answer and now it's exact answer as mine! :P – Himanshu Jul 18 '13 at 07:04
@hims056 because I realized that this is MySQL and not Oracle. – AllTooSir Jul 18 '13 at 07:06
really weird.. I exactly doing your answers with my database.. but I cant get the same result – Markie Mark Jul 18 '13 at 07:11

score 0 · Answer 3 · answered Jul 18 '13 at 08:39

0

Use This Query. Its worked for you.

select table1.* from table1 where table1.id not in (select id from table2)

answered Jul 18 '13 at 08:39

kabijoy

293
4
11

I remember using this on small database.. it works, but if you have bigger table this query gives you slow result and unwanted results. – Markie Mark Jul 19 '13 at 06:20

MYSQL unselect query from second table

3 Answers3

See this SQLFiddle