Learn Joins in SQL

Learn Joins in SQL

• 

What is joins?

Joins enables us to fetch data from multiple tables, in lot of situations you need to combine the multiple table data in a single select query, that we can do using sql joins. If you want to implement join between two or more tables in general those table should have some common field having same type of data type. You can do the join on any field only condition is data types of the fields should be same.

What are joins supported by SQL Server?

• Inner Join
• Outer Join (Left Outer Join, Right Outer Join, Full Outer Join)
• Cross Join
• Self Join
• Straight Join

Inner Join

Inner join allow you to select common records from both the tables based on field used to join. When you use two tables in inner join on some x field , it will select only those records from both the table where value for x field exists in both the table.

select table1.* ,table2.* from table1 inner join table2 on table1.fiel dname=table2.fieldname

Assume AdmisionTbl as follows –

Assume AdmissionFeesTbl table as follows –

The following command demonstrates an INNER JOIN:

SELECT AdmissionTbl.admission, AdmissionTbl.firstName, AdmissionTbl.lastName, AdmissionFeesTbl.amount_paid
FROM AdmissionTbl
INNER JOIN AdmissionFeesTbl
ON AdmissionTbl.admission = AdmissionFeesTbl.admission

It will output as follows –

LEFT OUTER JOIN

This type of join will return all rows from the left-hand table plus records in the right-hand table with matching values. For example:

SELECT AdmissionTbl.admission, AdmissionTbl.firstName, AdmissionTbl.lastName, AdmissionFeesTbl.amount_paid
FROM AdmissionTbl
Left Outer JOIN AdmissionFeesTbl
ON AdmissionTbl.admission = AdmissionFeesTbl.admission

The code returns the following:

RIGHT OUTER JOIN

This type of join returns all rows from the right-hand table and only those with matching values in the left-hand table. For example:

SELECT AdmissionTbl.admission, AdmissionTbl.firstName, AdmissionTbl.lastName, AdmissionFeesTbl.amount_paid
FROM AdmissionTbl
Right Outer JOIN AdmissionFeesTbl
ON AdmissionTbl.admission = AdmissionFeesTbl.admission

The statement returns the following:

FULL OUTER JOIN

This type of join returns all rows from both tables with NULL values where the JOIN condition is not true. For example:

SELECT AdmissionTbl.admission, AdmissionTbl.firstName, AdmissionTbl.lastName, AdmissionFeesTbl.amount_paid
FROM AdmissionTbl
Full Outer JOIN AdmissionFeesTbl
ON AdmissionTbl.admission = AdmissionFeesTbl.admission

The code returns the following result:

Straight Join

Straight join is the another form of inner join with different syntax, it will generate result similar to inner join it can be done as follows –

SELECT AdmissionTbl.admission, AdmissionTbl.firstName, AdmissionTbl.lastName, AdmissionFeesTbl.amount_paid
FROM AdmissionTbl,AdmissionFeesTbl
where AdmissionTbl.admission = AdmissionFeesTbl.admission

This will output –

Cross Join in SQL Server

The CROSS JOIN is used to generate a paired combination of each row of the first table with each row of the second table. This join type is also known as cartesian join.

SELECT
	select_list
FROM
	T1
CROSS JOIN T2;

In this example, we will consider the breakfast menu example again, which we mentioned in the earlier part of the article. Firstly, we will create the two-sample tables which contain the drink and meal names. After then, we will populate them with some sample data.

CREATE TABLE Meals(MealName VARCHAR(100))
CREATE TABLE Drinks(DrinkName VARCHAR(100))
INSERT INTO Drinks VALUES('Orange Juice'), ('Tea'), ('Cofee')
INSERT INTO Meals VALUES('Omlet'), ('Fried Egg'), ('Sausage')
SELECT * FROM Meals;
SELECT * FROM Drinks

The following query will join the Meals and Drinks table with the CROSS JOIN keyword and we will obtain all of the paired combinations of the meal and drink names.

SELECT * FROM Meals CROSS JOIN Drinks