Dreevoo.com | Online Learning and Knowledge Sharing
 
Home | Programs | Programming languages | PHP & MySQL | Using Inner, Left in Right Join in SQL
Guest
Click to view your profile
Topics
Programs
Languages
Recipes
Home
Shortcuts
 
 

Using Inner, Left in Right Join in SQL

Select database queries with Inner, Left and Right Join represent an important part of SQL syntax and come in very handy when you want to make more advanced queries.

 
  Author: podtalje | Version: # | 15th November 2013 |  
 
 
1.
 

Simple queries were described in previous lesson



In this lesson we will learn how to make queries using Inner, Left in Right Join.

For the purpose of this lesson we will again use tables my_table and country.

To see the complete structure of database please see the lesson mentioned above.

 
 
2.
 

So far we have learned that we can get data from two tables if we use select and match the fields in both tables.

SELECT *
FROM my_table AS a, country AS b
WHERE a.id_country = b.id

Instead of this we can also use 
INNER JOIN which is just another syntax for the example above. The logic behind this is the same and that is why also the end result will be the same.

SELECT *
FROM my_table AS a
INNER JOIN 
country AS b
ON 
a.id_country = b.id

 
 
3.
 

Of course we can add additional conditions at the end of the SQL statement also in this case by using keyword WHERE.

SELECT * FROM
my_table AS a
INNER JOIN country AS b
ON a.id_country = b.id
WHERE b.name = 'Japan'

 
 
4.
 

In the example above we are matching  a.id_country = b.id. 

The problem here is that if there is no match, there will be no returned data.

If we want to see the data from first table even if there is no match in second table, we need LEFT JOIN.

SELECT * FROM
my_table AS a
LEFT JOIN country AS b 
ON  a.id_country = b.id

This will return all the records from my_table. If there will be no match for field id, we will still get the records, just missing values will be empty (NULL).

 
 
5.
 

Similar logic also applies to RIGHT JOIN

In this case the query will return all the records from second table even if there is no match in the first table.

SELECT * FROM
my_table AS a
RIGHT JOIN country AS b 
ON  a.id_country = b.id


The main idea behind this is that by using LEFT and RIGHT JOIN we can make sure that we will always get all the data.

 
 
 
   
  Please login to post a comment
   
 
 
online learning made for people
Dreevoo.com | CONTRIBUTE | FORUM | INFO