Introduction To SQL Server Joins with Example

In SQL Server database, if records of two or more tables have to be combined, then the Join clause is used. It is a means that combine same fields of the tables can be combined using their common values. There are five types of join clause available for ANSI standard SQL Server: INNER, LEFT, RIGHT, FULL, and CROSS. A unique kind of Join clause named SELF-JOIN can be used that can combine itself.

Let me explain all joins with an example. Consider two tables below i.e. customer and bank_account

Table customer:

	id	Customer_name	business	salary
1	1	jublin	doctor	1000000
2	2	john	Bank manager	500000
3	3	michael	engineer	1000000
4	4	shown	clerk	300000

Table bank-account:

	id	Account_name	Account_id
1	1	SBI	3
2	2	RBI	2
3	3	BOI	1
4	4	HDFC	2

Inner Join:

This requires combining matching records of two tables. This is one of the commonly used join operation processed on tables, but it should not be considered as the best solution in every situation. Similar fields of the tables can be combined on the basis of join-predicate. When a query is passed, both the tables are combined with non-NULL values.

For example: We have two tables named Customers and Bank-account. If we want to join the tables that have customer_name, business, and account_name available depending upon the common IDS, the following query has to be run:

select customer_name, business, account_name from customer
inner join Bank_account on customer.id=Bank_account.account_id;

The join here is made on the ID column in both the tables.

	Customer_name	business	Account_name
1	michael	engineer	SBI
2	john	Bank manager	RBI
3	jublin	doctor	BOI
4	john	Bank manager	HDFC

Left Join:

In this case, records of the left table will be displayed irrespective of the fact that it does not have a matching entry in the right table. So, in the left join, all data from the ‘Customer’ table will be displayed but only the matching entries of the right table, i.e. Bank_account will be displayed.

It is important to understand here what does this left and right means here. In the query, that way you mention the table name depicts which is the right or left table. For example:

select customer_name, business, account_name from customer
left join Bank_account on customer.id=Bank_account.account_id;

Here the left table is customer because it is on the left side of the statement. In the output, notice that the IDs in both the table for the clerk did not match and thus account name is displayed as NULL.   

	Customer_name	Business	Account_name
1	Jublin	Doctor	BOI
2	John	Bank manager	RBI
3	john	Bank manager	HDFC
4	michael	engineer	SBI
5	shown	clerk	NULL

Right Join:

This is opposite of the left join. In this case, all entries in the right table will be displayed in the output irrespective of if the records match with the left table. The standard syntax that has to followed here is:

select customer_name, business, account_name from customer
right join Bank_account on customer.id=Bank_account.account_id;

Here you can notice that all records of the right table are displayed in spite of their non-matching behavior with the left table.

Full Join: 

Consider as the combination of inner, left, and right join. It will show all the matching, non-matching records from the left as well as right table. You can notice here that all entries of the tables are displayed irrespective of if they have matching values. John, the bank manager has an account in RBI as well as HDFC.  The standard syntax to follow is:

select customer_name, business, account_name from customer
full join Bank_account on customer.id=Bank_account.account_id;

Self join: It is used to join a table to itself if there are two tables so that one table could be renamed in the SQL statement.

I have used two IDs from tables customer and bank_account where one id is less than other tables account_id.

For example- select customer_name, business, account_name from customer, Bank_account where customer.id<Bank_account.account_id;

Cartesian Join:

Also known as the cross join, this does not require any specific condition to be fulfilled. The output of the two tables provided will be the Cartesian product if WHERE clause filters the rows. The rows of the left column are multiplied with the rows of the right column.

Conclusion: I have explained all above different types of SQL Server joins with their examples and use. Further will explain more about SQL server related information.