Type Of joins In SQL

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

The most common type of join is: SQL INNER JOIN (simple join). An SQL INNER JOIN return all rows from multiple tables where the join condition is met.

Let's look at a selection from the "Country" table:

Syntax For Inner Join

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

CountryId	Country
1	India
2	Japan
3	USA

Then, have a look at a selection from the "State" table:

id	CountryId	State
1	1	Maharashtra
2	1	Delhi
3	3	CaliforNia

Notice that the " CountryId " column in the "Country" table refers to the "

CountryId " in the "State" table. The relationship between the two tables

above is the "CustomerID" column.

Then, if we run the following SQL statement (that contains an INNER JOIN):

SELECT c. Country, s.State
FROM Country c
INNER JOIN State s
ON c.CountryId=s.CountryId;

and Your Ouput Will be Like this

Country	State
India	Maharashtra
India	Delhi
USA	CaliforNia

Different SQL JOINs

Before we continue with examples, we will list the types the different SQL JOINs you can use:

• INNER JOIN: Returns all rows when there is at least one match in BOTH tables
• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
• FULL JOIN: Return all rows when there is a match in ONE of the tables

SQL LEFT JOIN Syntax

• LEFT JOIN: Return all rows from the left table, and the matched rows from the right table

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

SQL RIGHT JOIN Syntax

• RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;


SQL FULL OUTER JOIN Syntax

• FULL JOIN: Return all rows when there is a match in ONE of the tables

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;