The JOIN clause is used with the SELECT statement to retrieve data from more than one table. This clause matches data from multiple tables. This results in the creation of a table, which is a combination of columns from multiple tables.
Uses of Joins: Join is a feature of SQL Server. It joins the tables by means of the combination of a primary key and a foreign key. The result of the query generates a temporary table containing the columns from two or more tables. An example of using the JOIN clause is as follows:
Suppose there is a database named Manufacture, which contains the tables Car and Dealer. The Car table has the following columns:
- CarID
- CarName
- CarModel
- CarPrice
The Dealer table has the following columns:
- DealerID
- DealerName
- Address
- Phone
- Mobile
- Commission
- CarID
The CarID column in the Car table is defined as the primary key, and the CarID column in the Dealer table is defined as the foreign key. If a user wants to retrieve the car name, car price, car model, dealer name, and commission, the following query should be executed:
SELECT CarName, CarModel, CarPrice, DealerName, Commission FROM Car JOIN Dealer WHERE Car.CarID=Dealer.CarID
Aliases: If two tables contain the same columns, it results in a conflict. To avoid this conflict, a user can use either the table names or aliases. The aliases improve the readability of a SQL statement. Aliases can be defined by using letters, numbers, or a combination of these. The following example will demonstrate the use of aliases:
SELECT c.CarName, c.CarModel, c.CarPrice, d.DealerName, d.Commission FROM Car c JOIN Dealer d WHERE Car.CarID=Dealer.CarID
In the above example, c is the alias for the Car table, and d is the alias for the Dealer table.
Types of Joins: The join types used in SQL Server are as follows:
Inner Joins: In this type of join, data in various columns are compared by using the comparison operators. The inner joins are also known as equi-joins. It returns only those rows that have an equal value. The following example will demonstrate the use of the inner join:
SELECT c.CarName, c.CarModel, c.CarPrice, d.DealerName, d.Commission FROM Car c INNER JOIN Dealer d ON c.CarID=d.CarID
Outer Joins: Outer joins return rows from one of the tables specified in the query. There are three types of outer joins as follows:
LEFT OUTER JOIN: When this join is used in a query, all the rows from the left table will be retrieved, and only the matching rows from the right table will be retrieved. If a row from the left table does not match with any of the rows of the right table, NULL is inserted in that row of the right table. The following example will demonstrate the use of the LEFT OUTER JOIN:
SELECT c.CarName, c.CarModel, c.CarPrice, d.DealerName, d.Commission FROM Car c LEFT OUTER JOIN Dealer d ON c.CarID=d.CarID
In the above example, all rows from the Car table will be retrieved, and only the matching rows from the Dealer table will be retrieved.
RIGHT OUTER JOIN: When this join is used in a query, all the rows from the right table will be retrieved, and only the matching rows from the left table will be retrieved. If a row from the right table does not match with any of the rows of the left table, NULL is inserted in that row of the left table. The following example will demonstrate the use of the RIGHT OUTER JOIN:
SELECT c.CarName, c.CarModel, c.CarPrice, d.DealerName, d.Commission FROM Car c RIGHT OUTER JOIN Dealer d ON c.CarID=d.CarID
In the above example, all rows from the Dealer table will be retrieved, and only the matching rows from the Car table will be retrieved.
FULL OUTER JOIN: When this join is used in a query, all the rows from all the tables specified in the query will be retrieved. The following example will demonstrate the use of the FULL OUTER JOIN:
SELECT c.CarName, c.CarModel, c.CarPrice, d.DealerName, d.Commission FROM Car c FULL OUTER JOIN Dealer d ON c.CarID=d.CarID
In the above example, all rows from the Dealer and Car tables will be retrieved.
Cross Joins: When this join is used in a query, the result is the multiplication of the number of rows from the left table with the number of rows from the right table. The following example will demonstrate this:
SELECT c.CarName, c.CarModel, c.CarPrice, d.DealerName, d.Commission FROM Car c CROSS JOIN Dealer d ON c.CarID=d.CarID
If the Car table contains ten rows and the Dealer table contains twelve rows, the result will be 120 rows.
Self Join: This join is used to join a table with itself. In this case, a user will have to use two unique aliases for the table. The following example will demonstrate this:
SELECT c1.CarName, c1.CarModel, c1.CarPrice FROM Car c1 INNER JOIN Car c2 ON c1.CarID=c2.CarID
