INNER JOIN vs LEFT JOIN vs RIGHT JOIN and FULL JOIN in SQL

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.

We can take each first four joins in Details :

We have two tables with the following values.

TableA

TableB

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               prasant       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.

If you liked the content and quality of this article, consider following me. Also if you’re watching this article as an anonymous user, consider signing up on Medium, that way you will be getting thousands of articles from hundreds of writers just like me.

Bye. Have a good day.