Inner join Query for mysql

Question

Im Having 2 Tables Table1

ID AnimalNAme  
1  Dog1    
1  Dog2    
2  cat1   
3  Cow1   
3  Cow2    
4  Rat1    
4  Rat2

Table2

ID AnimalNAme  
1  Dog1    
2  cat1   
3  Cow1   
4  Rat2

I need Output

1 Dog2  
3 Cow2   
4 Rat1

What is the condition for the desired output? There is no clear condition to filter out the list in the output. — Bere, Jun 25 '13 at 13:50
its working fine error i was making is iwas using left join and i had given Table1.AnimalName !=Table2.AnimalName and didnt specify "WHERE Table2.ID IS NULL" — vishu, Jun 27 '13 at 06:45

score 3 · Answer 1 · edited May 23 '17 at 10:33

I think you are looking for NOT EXISTS:

SELECT  ID, AnimalName
FROM    Table1
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    Table2
            WHERE   Table1.ID = Table2.ID
            AND     Table1.AnimalName = Table2.AnimalName
        );

Example on SQL Fiddle

This will return all rows from table1 that do not exist in table 2.

It is however, worth noting that in MySQL LEFT JOIN/IS NULL generally performs better than NOT EXISTS, and has the same effect. By stating that OUTERTABLE.Column IS NULL you are limiting the query to rows where there is no corresponding item in the outer table.

SELECT  Table1.ID, Table1.AnimalName
FROM    Table1
        LEFT JOIN Table2
            ON Table1.ID = Table2.ID
            AND Table1.AnimalName = Table2.AnimalName
WHERE   Table2.ID IS NULL;

Example on SQL Fiddle

@Bere Exactly, the required output only contains items that are in table1 and **NOT** in table2 — GarethD, Jun 25 '13 at 13:50

score 1 · Answer 2 · answered Jun 25 '13 at 13:46

1

It may be not the best option, but you can try the following query:

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

answered Jun 25 '13 at 13:46

Benz

2,317
12
19

score 1 · Answer 3 · answered Jun 25 '13 at 13:47

1

Left join and check for non matches?

SELECT 
FROM Table1 a
LEFT OUTER JOIN Table2 b
ON a.ID = b.ID
AND a.AnimalName = b.AnimalName
WHERE B.ID IS NULL

answered Jun 25 '13 at 13:47

Kickstart

21,403
2
21
33

score 1 · Answer 4 · answered Jun 25 '13 at 13:48

Since your question doesn't exactly state it, I can only guess by the data provided. How can I get a list of all animals that are in the first table and not in the second.

Do a left-join to the table you are comparing against and do a check for NULL in the second table.

select
      t1.id,
      t1.animalName
   from
      Table1 t1
         left join table2 t2
            on t1.id = t2.id
           AND t1.animalName = t2.animalName
   where
      t2.id IS NULL

I'm doing left-join on BOTH columns since who knows the quality of the data and having multiple animal names by the same name, but under different IDs... such as ID associated with a pet's owner.

Inner join Query for mysql

4 Answers4