SQL Server Tutorial: February 2022

SQL Server: Joins

This SQL Server tutorial explains how to use JOINS, both INNER and OUTER JOINS, in SQL Server (Transact-SQL) with syntax, visual illustrations, and examples.

Description

SQL Server (Transact-SQL) JOINS are used to retrieve data from multiple tables. A SQL Server JOIN is performed whenever two or more tables are joined in a SQL statement.

There are 4 different types of SQL Server joins:

SQL Server INNER JOIN (or sometimes called simple join)
SQL Server LEFT OUTER JOIN (or sometimes called LEFT JOIN)
SQL Server RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
SQL Server FULL OUTER JOIN (or sometimes called FULL JOIN)

So let's discuss SQL Server JOIN syntax, look at visual illustrations of SQL Server JOINS, and explore SQL Server JOIN examples.

INNER JOIN (simple join)

Chances are, you've already written a statement that uses an SQL Server INNER JOIN. It is the most common type of join. SQL Server INNER JOINS return all rows from multiple tables where the join condition is met.

Syntax

The syntax for the INNER JOIN in SQL Server (Transact-SQL) is:

SELECT columns
FROM table1 
INNER JOIN table2
ON table1.column = table2.column;

Visual Illustration

In this visual diagram, the SQL Server INNER JOIN returns the shaded area:

SQL Server

The SQL Server INNER JOIN would return the records where table1 and table2 intersect.

Example

Here is an example of an INNER JOIN in SQL Server (Transact-SQL):

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers 
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This SQL Server INNER JOIN example would return all rows from the suppliers and orders tables where there is a matching supplier_id value in both the suppliers and orders tables.

Let's look at some data to explain how the INNER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:

supplier_id	supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	NVIDIA

We have another table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

order_id	supplier_id	order_date
500125	10000	2003/05/12
500126	10001	2003/05/13
500127	10004	2003/05/14

If we run the SQL Server SELECT statement (that contains an INNER JOIN) below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

supplier_id	name	order_date
10000	IBM	2003/05/12
10001	Hewlett Packard	2003/05/13

The rows for Microsoft and NVIDIA from the supplier table would be omitted, since the supplier_id's 10002 and 10003 do not exist in both tables. The row for 500127 (order_id) from the orders table would be omitted, since the supplier_id 10004 does not exist in the suppliers table.

Old Syntax

As a final note, it is worth mentioning that the SQL Server INNER JOIN example above could be rewritten using the older implicit syntax as follows (but we still recommend using the INNER JOIN keyword syntax):

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;

LEFT OUTER JOIN

Another type of join is called a SQL Server LEFT OUTER JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax

The syntax for the LEFT OUTER JOIN in SQL Server (Transact-SQL) is:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the LEFT OUTER JOIN keywords are replaced with LEFT JOIN.

Visual Illustration

In this visual diagram, the SQL Server LEFT OUTER JOIN returns the shaded area:

SQL Server

The SQL Server LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1.

Example

Here is an example of a LEFT OUTER JOIN in SQL Server (Transact-SQL):

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
LEFT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This LEFT OUTER JOIN example would return all rows from the suppliers table and only those rows from the orders table where the joined fields are equal.

If a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as <null> in the result set.

Let's look at some data to explain how LEFT OUTER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:

supplier_id	supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	NVIDIA

We have a second table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

order_id	supplier_id	order_date
500125	10000	2003/05/12
500126	10001	2003/05/13

If we run the SELECT statement (that contains a LEFT OUTER JOIN) below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
LEFT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

supplier_id	supplier_name	order_date
10000	IBM	2003/05/12
10001	Hewlett Packard	2003/05/13
10002	Microsoft	<null>
10003	NVIDIA	<null>

The rows for Microsoft and NVIDIA would be included because a LEFT OUTER JOIN was used. However, you will notice that the order_date field for those records contains a <null> value.

RIGHT OUTER JOIN

Another type of join is called a SQL Server RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax

The syntax for the RIGHT OUTER JOIN in SQL Server (Transact-SQL) is:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the RIGHT OUTER JOIN keywords are replaced with RIGHT JOIN.

Visual Illustration

In this visual diagram, the SQL Server RIGHT OUTER JOIN returns the shaded area:

SQL Server

The SQL Server RIGHT OUTER JOIN would return the all records from table2 and only those records from table1 that intersect with table2.

Example

Here is an example of a RIGHT OUTER JOIN in SQL Server (Transact-SQL):

SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This RIGHT OUTER JOIN example would return all rows from the orders table and only those rows from the suppliers table where the joined fields are equal.

If a supplier_id value in the orders table does not exist in the suppliers table, all fields in the suppliers table will display as <null> in the result set.

Let's look at some data to explain how RIGHT OUTER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:

supplier_id	supplier_name
10000	Apple
10001	Google

We have a second table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

order_id	supplier_id	order_date
500125	10000	2013/08/12
500126	10001	2013/08/13
500127	10002	2013/08/14

If we run the SELECT statement (that contains a RIGHT OUTER JOIN) below:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

order_id	order_date	supplier_name
500125	2013/08/12	Apple
500126	2013/08/13	Google
500127	2013/08/14	<null>

The row for 500127 (order_id) would be included because a RIGHT OUTER JOIN was used. However, you will notice that the supplier_name field for that record contains a <null> value.

FULL OUTER JOIN

Another type of join is called a SQL Server FULL OUTER JOIN. This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.

Syntax

The syntax for the FULL OUTER JOIN in SQL Server (Transact-SQL) is:

SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the FULL OUTER JOIN keywords are replaced with FULL JOIN.

Visual Illustration

In this visual diagram, the SQL Server FULL OUTER JOIN returns the shaded area:

SQL Server

The SQL Server FULL OUTER JOIN would return the all records from both table1 and table2.

Example

Here is an example of a FULL OUTER JOIN in SQL Server (Transact-SQL):

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
FULL OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This FULL OUTER JOIN example would return all rows from the suppliers table and all rows from the orders table and whenever the join condition is not met, <nulls> would be extended to those fields in the result set.

If a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as <null> in the result set. If a supplier_id value in the orders table does not exist in the suppliers table, all fields in the suppliers table will display as <null> in the result set.

Let's look at some data to explain how FULL OUTER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:

supplier_id	supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	NVIDIA

We have a second table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

order_id	supplier_id	order_date
500125	10000	2013/08/12
500126	10001	2013/08/13
500127	10004	2013/08/14

If we run the SELECT statement (that contains a FULL OUTER JOIN) below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
FULL OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

supplier_id	supplier_name	order_date
10000	IBM	2013/08/12
10001	Hewlett Packard	2013/08/13
10002	Microsoft	<null>
10003	NVIDIA	<null>
<null>	<null>	2013/08/14

The rows for Microsoft and NVIDIA would be included because a FULL OUTER JOIN was used. However, you will notice that the order_date field for those records contains a <null> value.

The row for supplier_id 10004 would be also included because a FULL OUTER JOIN was used. However, you will notice that the supplier_id and supplier_name field for those records contain a <null> value.

SQL Server: PIVOT Clause

This SQL Server tutorial explains how to use the PIVOT clause in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) PIVOT clause allows you to write a cross-tabulation. This means that you can aggregate your results and rotate rows into columns.

Syntax

The syntax for the PIVOT clause in SQL Server (Transact-SQL) is:

SELECT first_column AS <first_column_alias>,
[pivot_value1], [pivot_value2], ... [pivot_value_n]
FROM 
(<source_table>) AS <source_table_alias>
PIVOT 
(
 aggregate_function(<aggregate_column>)
 FOR <pivot_column>
 IN ([pivot_value1], [pivot_value2], ... [pivot_value_n])
) AS <pivot_table_alias>;

Parameters or Arguments

first_column: A column or expression that will display as the first column in the pivot table.
first_column_alias: The column heading for the first column in the pivot table.
pivot_value1, pivot_value2, ... pivot_value_n: A list of values to pivot.
source_table: A SELECT statement that provides the source data for the pivot table.
source_table_alias: An alias for source_table.
aggregate_function: An aggregate function such as SUM, COUNT, MIN, MAX, or AVG.
aggregate_column: The column or expression that will be used with the aggregate_function.
pivot_column: The column that contains the pivot values.
pivot_table_alias: An alias for the pivot table.

Applies To

The PIVOT clause can be used in the following versions of SQL Server (Transact-SQL):

SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005

DDL/DML for Examples

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

Example

The PIVOT clause can be used in SQL Server (Transact-SQL).

Let's look at an example. If we had an employees table that contained the following data:

employee_number	last_name	first_name	salary	dept_id
12009	Sutherland	Barbara	54000	45
34974	Yates	Fred	80000	45
34987	Erickson	Neil	42000	45
45001	Parker	Sally	57500	30
75623	Gates	Steve	65000	30

And we ran the following SQL statement which creates a cross-tabulation query using the PIVOT clause:

SELECT 'TotalSalary' AS TotalSalaryByDept, 
[30], [45]
FROM
(SELECT dept_id, salary
 FROM employees) AS SourceTable
PIVOT
(
 SUM(salary)
 FOR dept_id IN ([30], [45])
) AS PivotTable;

It would return the following result:

TotalSalaryByDept	30	45
TotalSalary	122500	176000

This example would create a pivot table to display the total salary for dept_id 30 and dept_id 45. The results are displayed in one row with the two departments appearing each in their own column.

Now, let's break apart the PIVOT clause and explain how it worked.

Specify Columns in Cross-Tabulation Results

First, we want to specify what fields to include in our cross tabulation results. In this example, we want to include the literal value 'TotalSalary' as the first column in the pivot table. And we want to create one column for dept_id 30 and a second column for dept_id 45. This gives us 3 columns in our pivot table.

SELECT 'TotalSalary' AS TotalSalaryByDept, 
[30], [45]

Specify the Source Table Data

Next, we need to specify a SELECT statement that will return the source data for the pivot table.

In this example, we want to return the dept_id and salary values from the employees table:

(SELECT dept_id, salary
 FROM employees) AS SourceTable

You must specify an alias for the source query. In this example, we have aliased the query as SourceTable.

Specify Aggregate Function

Next, we need to specify what aggregate function to use when creating our cross-tabulation query. You can use any aggregate such as SUM, COUNT, MIN, MAX, or AVG functions.

In this example, we are going to use the SUM function. This will sum the salary values:

PIVOT
(SUM(salary)

Specify Pivot Values

Finally, we need to specify what pivot values to include in our results. These will be used as the column headings in our cross-tabulation query.

In this example, we are going to return only the dept_id values of 30 and 45. These values will become our column headings in our pivot table. Also, note that these values are a finite list of the dept_id values and will not necessarily contain all possible values.

FOR dept_id IN ([30], [45])

Now when we put it all together, we get the following pivot table:

TotalSalaryByDept	30	45
TotalSalary	122500	176000

SQL Server: Subqueries

This SQL Server tutorial explains how to use subqueries in SQL Server (Transact-SQL) with syntax and examples.

What is a subquery in SQL Server?

In SQL Server, a subquery is a query within a query. You can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.

Note

In SQL Server (Transact-SQL), a subquery is also called an INNER QUERY or INNER SELECT.
In SQL Server (Transact-SQL), the main query that contains the subquery is also called the OUTER QUERY or OUTER SELECT.

WHERE clause

Most often, the subquery will be found in the WHERE clause. These subqueries are also called nested subqueries.

For example:

SELECT p.product_id, p.product_name
FROM products p
WHERE p.product_id IN
   (SELECT inv.product_id
    FROM inventory inv
    WHERE inv.quantity > 10);

The subquery portion of the SELECT statement above is:

(SELECT inv.product_id
 FROM inventory inv
 WHERE inv.quantity > 10);

This subquery allows you to find all product_id values from the inventory table that have a quantity greater than 10. The subquery is then used to filter the results from the main query using the IN condition.

This subquery could have alternatively been written as an INNER join as follows:

SELECT p.product_id, p.product_name
FROM products p
INNER JOIN inventory inv
ON p.product_id = inv.product_id
WHERE inv.quantity > 10;

This INNER JOIN would run more efficiently than the original subquery. It is important to note, though, that not all subqueries can be rewritten using joins.

FROM clause

A subquery can also be found in the FROM clause. These are called inline views.

For example:

SELECT suppliers.supplier_name, subquery1.total_amt
FROM suppliers,
 (SELECT supplier_id, SUM(orders.amount) AS total_amt
  FROM orders
  GROUP BY supplier_id) subquery1
WHERE subquery1.supplier_id = suppliers.supplier_id;

In this example, we've created a subquery in the FROM clause as follows:

(SELECT supplier_id, SUM(orders.amount) AS total_amt
 FROM orders
 GROUP BY supplier_id) subquery1

This subquery has been aliased with the name subquery1. This will be the name used to reference this subquery or any of its fields.

SELECT clause

A subquery can also be found in the SELECT clause. These are generally used when you wish to retrieve a calculation using an aggregate function such as the SUM, COUNT, MIN, or MAX function, but you do not want the aggregate function to apply to the main query.

For example:

SELECT e1.last_name, e1.first_name,
  (SELECT MAX(salary)
   FROM employees e2
   WHERE e1.employee_id = e2.employee_id) subquery2
FROM employees e1;

In this example, we've created a subquery in the SELECT clause as follows:

(SELECT MAX(salary)
 FROM employees e2
 WHERE e1.employee_id = e2.employee_id) subquery2

The subquery has been aliased with the name subquery2. This will be the name used to reference this subquery or any of its fields.

The trick to placing a subquery in the select clause is that the subquery must return a single value. This is why an aggregate function such as the SUM, COUNT, MIN, or MAX function is commonly used in the subquery.

SQL Server: EXCEPT Operator

This SQL Server tutorial explains how to use the EXCEPT operator in SQL Server (Transact-SQL) with syntax and examples.

Description

The SQL Server (Transact-SQL) EXCEPT operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement will define a dataset. The EXCEPT operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset.

Except Query

Explanation: The EXCEPT query will return the records in the blue shaded area. These are the records that exist in Dataset1 and not in Dataset2.

Each SELECT statement within the EXCEPT query must have the same number of fields in the result sets with similar data types.

Syntax

The syntax for the EXCEPT operator in SQL Server (Transact-SQL) is:

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
EXCEPT
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

Parameters or Arguments

expressions: The columns or calculations that you wish to compare between the two SELECT statements. They do not have to be the same fields in each of the SELECT statements, but the corresponding columns must be similar data types.
tables: The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
WHERE conditions: Optional. The conditions that must be met for the records to be selected.

Note

There must be same number of expressions in both SELECT statements.
The corresponding columns in each of the SELECT statements must have similar data types.
The EXCEPT operator returns all records from the first SELECT statement that are not in the second SELECT statement.
The EXCEPT operator in SQL Server is equivalent to the MINUS operator in Oracle.

Example - With Single Expression

Let's look at an example of the EXCEPT operator in SQL Server (Transact-SQL) that returns one field with the same data type.

For example:

SELECT product_id
FROM products
EXCEPT
SELECT product_id
FROM inventory;

This EXCEPT operator example returns all product_id values that are in the products table and not in the inventory table. What this means is that if a product_id value existed in the products table and also existed in the inventory table, the product_id value would not appear in the EXCEPT query results.

Example - With Multiple Expressions

Next, let's look at an example of an EXCEPT query in SQL Server (Transact-SQL) that returns more than one column.

For example:

SELECT contact_id, last_name, first_name
FROM contacts
WHERE last_name = 'Anderson'
EXCEPT
SELECT employee_id, last_name, first_name
FROM employees;

In this EXCEPT example, the query will return the records in the contacts table with a contact_id, last_name, and first_name value that does not match the employee_id, last_name, and first_name value in the employees table.

Example - Using ORDER BY

Finally, let's look at how to use the ORDER BY clause in an EXCEPT query in SQL Server (Transact-SQL).

For example:

SELECT supplier_id, supplier_name
FROM suppliers
WHERE state = 'Florida'
EXCEPT
SELECT company_id, company_name
FROM companies
WHERE company_id <= 400
ORDER BY 2;

In this EXCEPT example, since the column names are different between the two SELECT statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the ORDER BY 2.

The supplier_name / company_name fields are in position #2 in the result set.