SQL For Beginners: Joins

In the previous article,

SQL For Beginners - Aliases

We learned about SQL Aliases. In this article, we will learn about SQL Joins and practically see the usage of Aliases in Joins.

Our data may be stored in multiple tables. In many situations, we need the data from multiple tables together at a single place. In order to retrieve data from multiple related tables and display it as a single table, we use Joins. We can apply joins on the tables having a common attribute.

There are different types of joins:

Inner Join
Outer Join
Cross Join
Equi Join
Self Join

Syntax:

SELECT column1, column2, .....
FROM Table1 JOIN Table2
ON Table1.CommonColumn Join_Operator Table2.CommonColumn
[Where search_condition]
where,

Table1 and Table2 are the names of the table that are to be joined.
Table1.CommonColumn and Table2.CommonColumn are the names of the common column on the basis of which the join is to be applied.
Join_Operator is the comparison operator based on which the join will be applied.

INNER JOIN:

An inner join retrieves results from multiple tables having a common attribute (common column).

Example:

Select o.orderid, o.orderdate, c.custid, c.contactname
from Sales.Orders as o Inner Join Sales.Customers as c
On o.custid=c.custid;

NOTE:

You will require TSQL2012 Sample database to execute these queries and see the difference between the various types of joins. You can download the database from here.

OUTER JOIN:

Outer Join displays all the records of one table and matching records from another table. There are three types of outer joins: Left outer join, Right outer join and Full outer join.

Left Outer Join:

Select o.orderid, o.orderdate, c.custid, c.contactname
from Sales.Orders as o Left Outer Join Sales.Customers as c
On o.custid=c.custid;

Here, all the rows of the Orders table will be displayed and only the matching rows from the Customers table will be displayed. It will display NULL for the Customers table (Right side table) for the non matching results.

Right Outer Join:

Select o.orderid, o.orderdate, c.custid, c.contactname
from Sales.Orders as o Right Outer Join Sales.Customers as c
On o.custid=c.custid;

Here, all the rows of the Customers table will be displayed and only the matching rows from the Orders table will be displayed. It will display NULL for the Orders table (Left side table) for the non matching results.

Full Outer Join:

Select o.orderid, o.orderdate, c.custid, c.contactname
from Sales.Orders as o Full Outer Join Sales.Customers as c
On o.custid=c.custid;

Here, all the matching and non matching rows will be returned from both the tables. The matching rows will be displayed only once and the non matching rows for which data is not available will be NULL.

CROSS JOIN:

A Cross Join returns the cartesian product of the two tables. All the rows of one table will be joined with each row of the other table.

Select o.orderid, o.orderdate, c.custid, c.contactname
from Sales.Orders as o Cross Join Sales.Customers as c;

EQUI JOIN:

An Equi Join is similar to Inner Join. It joins the table with the help of a foreign key. The key difference is that in equi join only the Equality (=) operator can be used as the Join operator, whereas in inner join we can also use conditional operators.

SELF JOIN:

When we have to join a table with itself, then we use SELF Join.

Select c1.custid, c1.contactname, c2.custid, c2.contactname
From Sales.Customers as c1 Join Sales.Customers as c2
on c1.custid = c2.custid;

Here, we use the same table name twice with the help of SQL Aliases. This was a small overview of SQL Joins.