Explain SQL Server JOINs Fundamentals with Examples

By using joins, you can retrieve data from two or more tables based on logical relationships between the tables. A join condition defines the way two tables are related in a query by:

• Specifying the column from each table to be used for the join. 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.

Inner joins can be specified in either the FROM or WHERE clauses.

 Outer joins can be specified in the FROM clause only.

Tables in a database are often related to each other with keys.

SQL JOIN  joins together two tables on a matching table column, ultimately forming one single temporary table. The key word here is temporary. The tables themselves remain intact, and running a JOIN query does not in any way change the data or table structure. 

JOIN is another way to select specific data from two or more relational tables.

Depending on whether we add any join condition or not there are following join types (yellow in model):

• CROSS JOIN (synonyms also CARTESIAN JOIN, CARTESIAN PRODUCT) - there isn't any join condition or it is always true. All other join types degrade to CROSS JOINS as soon as join condition (-s) is (are) always true.
• JOIN WITH RESTRICTION - there is applied join condition to joined tables. One can write join with restriction using different syntactic notations. As already said above every join with restriction may degrade to cross join.
• NATURAL JOIN - syntactic notation joining source tables on all columns having the same name. This can be quite dangerous as explained below in the chapter for Natural join. Natural joins always are Equi joins.
• QUALIFIED JOIN - user has possibility to define which columns are used in join condition.
• NAMED COLUMNS JOIN - syntactic notation joining source tables on user defined columns having the same name. This is less dangerous than Natural join and just short form of writing Equi joins on some common columns joined together. Named columns joins always are Equi joins.
• CONDITIONAL JOIN - fully controllable syntax by user. This is the most widespread and most useful syntactic convention. Depending on used predicates in join condition it may be Equi join as well as Non-equi join.

   Join Types:

   Inner Join (equi join) Or Simple Join
   Outer Join: Left Outer Join, Right Outer Join
   Cross Join
   Full Join
   Self Join: A self join is a join in which a table is joined with itself. For example, when you require     details about an employee and his manager (also an employee).

1) Inner Join OR Simple Join OR Equi joins

It is a simple sql join condition which uses the equal sign as the comparison operator. Two types of equi joins are SQL Outer join and SQL Inner join.

For example: You can get the information about a customer who purchased a product and the quantity of product.

Look at the "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Note that the "P_Id" column is the primary key in the "Persons" table. This means that no two rows can have the same P_Id. The P_Id distinguishes two persons even if they have the same name.

Next, we have the "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Note that the "O_Id" column is the primary key in the "Orders" table and that the "P_Id" column refers to the persons in the "Persons" table without using their names.

Notice that the relationship between the two tables above is the "P_Id" column.

INNER JOIN:

The INNER JOIN keyword return rows when there is at least one match in both tables.

SQL INNER JOIN Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the persons with any orders.

We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName

The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678

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.

SQL LEFT JOIN Keyword

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).

SQL LEFT JOIN Syntax

SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name

PS: In some databases LEFT JOIN is called LEFT OUTER JOIN.

---

SQL LEFT JOIN Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the persons and their orders - if any, from the tables above.

We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName

The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678
Svendson	Tove

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

SQL RIGHT JOIN Keyword

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).

SQL RIGHT JOIN Syntax

SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name

PS: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

---

SQL RIGHT JOIN Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the orders with containing persons - if any, from the tables above.

We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName

The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678
		34764

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

SQL FULL JOIN Keyword

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

A "FULL JOIN" is an outer join that takes *all* data from both tables, matched where it can, as opposed to a LEFT or RIGHT join that takes all the data from one table, *and* any matching records from the other table,

<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->

SQL FULL JOIN Syntax

SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name

---

SQL FULL JOIN Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the persons and their orders, and all the orders with their persons.

We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName

The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678
Svendson	Tove
		34764

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

 

SQL Self-join

Description:SQL self-join simply is a normal join which is used to join a table to itself. The SQL self-join can be done by using SQL table aliases to treat one table like a different table and then join them together. SQL self-join can be any form of join such as SQL inner join, SQL outer join… so you can apply any join to the SQL self-join.

Here is common syntax of SQL self-join:

SELECT column_list FROM table_A AS A

INNER JOIN table_A AS B

  ON A.column_name1 = B.column_name2, ...

WHERE row_conditions

SQL self-join is very useful when you want to retrieve related data storing in one table such as organizational structure. In our database sample, we have employees table which stores not only employee data but also organizational structure. The column reportsTo specifies the manager of  an employee and is referenced to employeeIDcolumn. 

SELECT concat(e.firstname, e.lastname) employee,        

       concat(m.firstname,m.lastname) manager

FROM employees e

INNER JOIN employees m ON m.employeeId = e.reportsTo

CROSS JOIN:

Description:SQL CROSS JOIN will return all records where each row from the first table is combined with each row from the second table. Which also mean CROSS JOIN returns the Cartesian product of the sets of rows from the joined tables.

EXAMPLE :Let's try with 2 tables below:

Table 1: GameScores

PlayerName	DepartmentId	Scores
Jason	1	3000
Irene	1	1500
Jane	2	1000
David	2	2500
Paul	3	2000
James	3	2000

Table 2: Departments

DepartmentId	DepartmentName
1	IT
2	Marketing
3	HR

SQL statement :SELECT* FROM GameScores CROSS JOIN Departments

Result:

PlayerName	DepartmentId	Scores	DepartmentId	DepartmentName
Jason	1	3000	1	IT
Irene	1	1500	1	IT
Jane	2	1000	1	IT
David	2	2500	1	IT
Paul	3	2000	1	IT
James	3	2000	1	IT
Jason	1	3000	2	Marketing
Irene	1	1500	2	Marketing
Jane	2	1000	2	Marketing
David	2	2500	2	Marketing
Paul	3	2000	2	Marketing
James	3	3000	2	Marketing
Jason	1	3000	3	HR
Irene	1	1500	3	HR
Jane	2	1000	3	HR
David	2	2500	3	HR
Paul	3	2000	3	HR
James	3	3000	3	HR