How To Use SQL Joins

SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.

The process of forming pairs of rows by matching the contents of related columns is called joining the tables. The resulting table (containing data from both of the original tables) is called a join between the two table.

Join are the only mechanism for exercising cross-table data relationships. A typical join condition specifies a foreign key from one table and its associated key in the other table. Specifying a logical operator (for example, = or <>,) to be used in comparing values from the columns.

Different types of  SQL  join

• JOIN: Return rows when there is at least one match in both tables.
• INNER JOIN: Return rows when there is at least one match in both tables.
• LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table.
• RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table.
• FULL JOIN: Return rows when there is a match in one of the tables.

Before we see the join example, first will create two tables.  They are :

CREATE TABLE table_name

(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
….
)

We create a first table called “Persons” that contains five columns: P_Id, LastName, FirstName, Address, and City.

CREATE TABLE Persons
(
P_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

We will store the Persons details in the Persons table like P_Id, lastName and address. In the Persons table P_Id column is the identity column. Hence P_Id is the Primary key.

INSERT INTO table_name (column1, column2, column3,…)
VALUES (value1, value2, value3,…)

Now we  insert a values  in row in the “Persons” table.

INSERT INTO Persons
VALUES (4,’Nilsen’, ‘Pettersen’ , ‘Stavanger’, ‘ Hansen’)

We will create second Order table, and in the Orders table, order information will be stored. In the order table CompanyId will have the value of the id column of the company table. Hence P_Id is the foreign key.

INNER JOIN or JOIN

The INNER JOIN key word will return there is at least one match in both tables.

Syntax

SELECT colume_name(s)

FROM table_name1

INNER JOIN table_name2

ON table_name1.column_name=table_name2.column_name

or

SELECT colume_name(s)

FROM table_name1

JOIN table_name2

ON table_name1.column_name=table_name2.column_name

Example

SELECT Persons. FirstName, Persons. LastName, Order. Order_no

FROM Persons

INNER JOIN Order

ON Persons.P_Id=Order.P_Id

Order By Person. LastName

The INNER JOIN keyword return rows when there is at least one match in both tables. If there are rows in “Persons” that do not have matches in “Orders“, those rows will NOT be listed.

LEFT JOIN

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).

Syntex

SELECT column_name(s)

FROM table_name1

LEFT JOIN table_name2

ON table_name1.column_name=table_name2.column_name

Example

SELECT Persons. FirstName, Persons. LastName, Order. Order_no

FROM Persons

LEFT JOIN Order

ON Persons.P_Id=Order.P_Id

Order By Person. LastName

In the Left Join example result “Nilsen” details included even though there is no “Order_no”  available in the orders table.

RIGHT JOIN

The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are no matches in the left table (table_name1).

Syntex

SELECT column_name(s)

FROM table_name1

RIGHT JOIN table_name2

ON table_name1.column_name=table_name2.column_name

Example

SELECT Persons. FirstName, Persons. LastName, Order. Order_no

FROM Persons

RIGHT JOIN Order

ON Persons.P_Id=Order.P_Id

Order By Person. LastName

It will return all the rows from the right table (Order) even if there are no rows in the left table (Persons)

In the orders table we will not have the orders for Nilsen. When you use the left join Nilsen will  be included in the list. But if we use right join then they will not  be included.

The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if there are no matches in the left table (Persons).

FULL JOIN

The FULL JOIN keyword return rows when there is a match in one of the tables.

Syntex

SELECT column_name(s)

FROM table_name1

FULL JOIN table_name2

ON table_name1.column_name=table_name2.column_name

Example

SELECT Persons. FirstName, Persons. LastName, Order. Order_no

FROM Persons

FULL JOIN Order

ON Persons.P_Id=Order.P_Id

Order By Person. LastName

FULL JOIN will return all the rows from the left table Persons and all the rows from the right table Orders. If there are rows in Orders that do not have matches in Persons, or if there are rows in Persons that do not have matches in Orders, those rows will be listed as well.

In the persons table we are not having persons last and firstname for some orders. For that case, Null values is included in the LastName and FirstName columns. Like this same in order_no column, Null values is included.  As a result, all of the rows from the Orders table, and all the rows from persons table displayed.