Quick Solution Providers
Complete Solution for your problems under one roof

MySQL Left Outer Join

Nov 16, 2009 Home -> Mysql

Joins are very interesting topic in mysql.In my 4 years exp. i feel that every body knows about joins (experts and freshers) but freshers use joins very few.This may be because of their low thinking capability in starting or may be they find joins more typical then their local logic.Maximum beginners try to put their own logic rather then joins.

Today we will talk about left outer join .Not very common join but very effective join.
Suppose you have 2 tables. First “table1″ with 2 fields ‘id’ and ‘name.’ . Second “table2″ with 2 fields ‘id’ and ’salary’.In table1 “id” is primary key and in table2 field “id” is foreign key.

Suppose structure for 2 tables are like this:
Table1:                                                        Table2:
id       name                                                id       salary
————–                                                  —————–
1         aa                                                     2        2000
2          bb                                                   5         3000
3          cc                                                   NULL     NULL
4          cc
5          cc
6          cc
7          cc
8          cc
9          cc
10          cc

Now you want to fetch all records from table1 where id in table1 is not equal to id from table2 means we have to fetch records from both table1 where table1.id!=table2.id. One important thing about table2 that there may be null in id column.
Lot of programmers will try to do this by their logic means they will not use joins but try to fetch records saperatly from both tables and then make array and then write a logic to remove duplicate entries from array and then again run a query to fetch records for ids exists in final array.This is my thinking , may be they use another logic.

This process will take time to write whole code , also this will take extra space in file and we have to write more queries to fetch records.
Now here our left outer join is very effective solution.Using left outer join only single query will fetch all the records according to our need.
Here is the query:
“select table1.* from table1 LEFT OUTER JOIN table2 ON table1.id = table2.id WHERE table2.id IS NULL”

This will give you result like this.
Table1:
id       name
————–
1         aa
3          cc
4          cc
6          cc
7          cc
8          cc
9          cc
10          cc

This will give me a list off all the records that are in table1 but don’t have an entry in table2.

Just use this join and it will save your time and space.

Related posts:

1. How to insert data from one table to another table in mysql
2. Different text fields in Mysql and their field size

Tags: fetch, inner join, join, joins, left join, left outer join, Mysql, mysql join, mysql joins, records, right join, right outer join, self join