mysql - What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

ID : 458

viewed : 186

Tags : mysqljoininner-joinouter-joinmysql

Top 5 Answer for mysql - What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

vote vote

99

Reading this original article on The Code Project will help you a lot: Visual Representation of SQL Joins.

alt text

Also check this post: SQL SERVER – Better Performance – LEFT JOIN or NOT IN?.

Find original one at: Difference between JOIN and OUTER JOIN in MySQL.

vote vote

85

An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.

There are different types of joins available in SQL:

INNER JOIN: returns rows when there is a match in both tables.

LEFT JOIN: returns all rows from the left table, even if there are no matches in the right table.

RIGHT JOIN: returns all rows from the right table, even if there are no matches in the left table.

FULL JOIN: combines the results of both left and right outer joins.

The joined table will contain all records from both the tables and fill in NULLs for missing matches on either side.

SELF JOIN: joins a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

CARTESIAN JOIN: returns the Cartesian product of the sets of records from the two or more joined tables.

We can take each first four joins in Details :

We have two tables with the following values.

TableA

id  firstName                  lastName ....................................... 1   arun                        prasanth                  2   ann                         antony                    3   sruthy                      abc                       6   new                         abc                                            

TableB

id2 age Place ................ 1   24  kerala 2   24  usa 3   25  ekm 5   24  chennai 

....................................................................

INNER JOIN

Note : gives the intersection of the two tables, i.e. rows TableA and TableB have in common.

Syntax

SELECT table1.column1, table2.column2...   FROM table1  INNER JOIN table2     ON table1.common_field = table2.common_field; 

Apply it in our sample table :

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place   FROM TableA  INNER JOIN TableB     ON TableA.id = TableB.id2; 

Result

firstName       lastName       age  Place .............................................. arun            prasanth        24  kerala ann             antony          24  usa sruthy          abc             25  ekm 

LEFT JOIN

Note : gives all selected rows in TableA, plus any common selected rows in TableB.

Syntax

SELECT table1.column1, table2.column2...   FROM table1   LEFT JOIN table2     ON table1.common_field = table2.common_field; 

Apply it in our sample table :

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place   FROM TableA   LEFT JOIN TableB     ON TableA.id = TableB.id2; 

Result

firstName                   lastName                    age   Place ............................................................................... arun                        prasanth                    24    kerala ann                         antony                      24    usa sruthy                      abc                         25    ekm new                         abc                         NULL  NULL 

RIGHT JOIN

Note : gives all selected rows in TableB, plus any common selected rows in TableA.

Syntax

SELECT table1.column1, table2.column2...   FROM table1  RIGHT JOIN table2     ON table1.common_field = table2.common_field; 

Apply it in our sample table :

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place   FROM TableA  RIGHT JOIN TableB     ON TableA.id = TableB.id2; 

Result

firstName                   lastName                    age     Place ............................................................................... arun                        prasanth                    24     kerala ann                         antony                      24     usa sruthy                      abc                         25     ekm NULL                        NULL                        24     chennai 

FULL JOIN

Note : returns all selected values from both tables.

Syntax

SELECT table1.column1, table2.column2...   FROM table1   FULL JOIN table2     ON table1.common_field = table2.common_field; 

Apply it in our sample table :

SELECT TableA.firstName,TableA.lastName,TableB.age,TableB.Place   FROM TableA   FULL JOIN TableB     ON TableA.id = TableB.id2; 

Result

firstName                   lastName                    age    Place ............................................................................... arun                        prasanth                    24    kerala ann                         antony                      24    usa sruthy                      abc                         25    ekm new                         abc                         NULL  NULL NULL                        NULL                        24    chennai 

Interesting Fact

  • For INNER joins the order doesn't matter.
  • For (LEFT, RIGHT or FULL) OUTER joins, the order matters.

Better to go check this Link it will give you interesting details about join order.

vote vote

78

INNER JOIN gets all records that are common between both tables based on the supplied ON clause.

LEFT JOIN gets all records from the LEFT linked and the related record from the right table ,but if you have selected some columns from the RIGHT table, if there is no related records, these columns will contain NULL.

RIGHT JOIN is like the above but gets all records in the RIGHT table.

FULL JOIN gets all records from both tables and puts NULL in the columns where related records do not exist in the opposite table.

vote vote

63

If you need the hash parameters present in the URL, window.location.href may be a better choice.

window.location.pathname => /search  window.location.href   => www.website.com/search#race_type=1 
vote vote

60

You'll want to use JavaScript's built-in window.location object.

Top 3 video Explaining mysql - What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

Related QUESTION?