SQL Server queries using the JOIN Operator

Example of queries using the JOIN operator written in T-SQL language, the goal is to join data from multiple tables.

In T-SQL, the Microsoft’s SQL Server programming language, a Join query between two or more tables is done using the JOIN keyword operator. This connection is made according to conditions specified in the WHERE clause. There are several variants of the JOIN operator, each with its own characteristics and uses.

1. Create the sample tables for customers and orders with SSMS

Here’s how to create two example tables used in the following JOIN queries.

Create the Customers table

Run this code in SSMS for example to create the customers table and insert rows into it.

-- Create the customers table
CREATE TABLE customers (
id INT PRIMARY KEY,
name VARCHAR(255),
city VARCHAR(255)
);

-- Insert rows into the customers table
INSERT INTO customers
(id, name, city)
VALUES
(1, 'Dupont', 'Paris'),
(2, 'Durand', 'Lyon'),
(3, 'Martin', 'Marseille');

Create the Orders table

Next, create the orders table with the T-SQL code below.

-- Create the orders table
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
amount DECIMAL(10, 2),
order_date DATE
);

-- Insert rows into the orders table
INSERT INTO orders
(id, customer_id, amount, order_date)
VALUES
(1, 1, 100.00, '2022-01-01'),
(2, 2, 50.00, '2022-01-02'),
(3, 1, 200.00, '2022-01-03'),
(4, 3, 75.00, '2022-01-04');

2. SQL Server Join of type INNER JOIN

The INNER JOIN operator allows linking rows from two tables, returning only the rows that have a match in both tables. A join condition is often used in the ON clause to specify how the rows are linked.

SELECT *
FROM customers c
INNER JOIN orders o
ON c.id = o.customer_id;

The result of the query with the INNER JOIN operator will therefore include 4 rows, like this:

idnamecityidcustomer_idamountorder_date
1DupontParis11100.002022-01-01
2DurandLyon2250.002022-01-02
1DupontParis31200.002022-01-03
3MartinMarseille4375.002022-01-04

3. T-SQL LEFT JOIN Operator

The LEFT JOIN operator allows linking rows from two tables, returning all rows from the left table and the matches in the right table if they exist. If a row in the left table does not have a match in the right table, a NULL row will be created in the result for this row.

SELECT *
FROM customers c
LEFT JOIN orders o
ON c.id = o.customer_id;

The result of this SQL LEFT JOIN query written in T-SQL.

idnamecityidcustomer_idamountorder_date
1DupontParis11100.002022-01-01
2DurandLyon2250.002022-01-02
1DupontParis31200.002022-01-03
3MartinMarseille4375.002022-01-04
4DupuisBordeauxNULLNULLNULLNULL

4. SQL Server RIGHT JOIN Operator

The RIGHT JOIN operator is similar to the LEFT JOIN, but it returns all rows from the right table, as well as the matches in the left table if they exist. If a row in the right table does not have a match in the left table, a NULL row will be created in the result for this row.

SELECT *
FROM customers c
RIGHT JOIN orders o
ON c.id = o.customer_id;

The result of the right join query above will give a result like this one, using our sample tables:

idnamecityidcustomer_idamountorder_date
1DupontParis11100.002022-01-01
2DurandLyon2250.002022-01-02
1DupontParis31200.002022-01-03
3MartinMarseille4375.002022-01-04
NULLNULLNULL5225.002022-01-05

You may also be interested in the SQL Server data type and their management.

SQL Server data types with code examples to create columns

5. SQL Server CROSS JOIN Join

The T-SQL CROSS JOIN operator allows you to link all rows from the left table with all rows from the right table, without using a join condition.

SELECT *
FROM customers c
CROSS JOIN orders o;

The result of this CROSS JOIN type query, executed on the example tables, will be as follows.

idnamecityidcustomer_idamountorder_date
1DupontParis11100.002022-01-01
1DupontParis2250.002022-01-02
1DupontParis31200.002022-01-03
1DupontParis4375.002022-01-04
2DurandLyon11100.002022-01-01
2DurandLyon2250.002022-01-02
2DurandLyon31200.002022-01-03
2DurandLyon4375.002022-01-04
3MartinMarseille11100.002022-01-01
3MartinMarseille2250.002022-01-02
3MartinMarseille31200.002022-01-03
3MartinMarseille4375.002022-01-04

It’s important to note that the CROSS JOIN operator can quickly generate a very large number of rows in the result if the involved tables are large. It is therefore recommended to only use it when really necessary.

6. The T-SQL FULL OUTER JOIN query

The FULL OUTER JOIN operator allows linking the rows of two tables by returning all the rows from the left table, all the rows from the right table, and the matches between the two tables if they exist. If a row from one table has no match in the other table, a NULL row will be created in the result for that row.

SELECT *
FROM clients c
FULL OUTER JOIN orders o
ON c.id = o.client_id;

The result of the T-SQL query with FULL OUTER JOIN will be as follows.

idnamecityidcustomer_idamountorder_date
1DupontParis11100.002022-01-01
2DurandLyon2250.002022-01-02
1DupontParis31200.002022-01-03
3MartinMarseille4375.002022-01-04
4DupuisBordeauxNULLNULLNULLNULL

7. SQL Server query with SELF JOIN or Auto-Join

The SELF JOIN operator allows linking the rows of the same table using two different aliases for the table. A join condition can be used in the ON clause to specify how the rows are linked. This query is also called an auto-join.

SELECT c1.name, c2.name
FROM   clients c1
JOIN  clients c2
   ON c1.town = c2.town
WHERE c1.name != c2.name;

The result of this query with the auto-join will be like this.

namename
DupontMartin
MartinDupont

It is important to note that these query examples with the SQL Server JOIN operator are just an introduction to the operators in T-SQL. It is also possible to perform joins with Integration Services, particularly with the SSIS Merge Join component.

Conclusion on SQL Server Join Operators

A SQL Server join with the T-SQL JOIN operator allows combining data from different tables into a single query. There are several types of JOIN operators, each with its own characteristics and uses. Here are some good practices and benefits to remember about using JOIN operators in T-SQL:

  • The INNER JOIN operator is used to return rows that have a match in both tables. This operator is the most used and the most performant.
  • Use the LEFT JOIN operator to return all the rows from the left table, as well as all the matching rows from the right table. This operator is useful for including all the rows from the left table, even if they have no match in the right table.
  • Use the RIGHT JOIN operator in a manner similar to the LEFT JOIN operator, but by reversing the order of the tables.
  • And the FULL OUTER JOIN operator is useful to return all rows from both tables, whether or not they have a match. This operator is less performant than the other JOIN operators and is generally not recommended in common queries.
  • Finally, use the CROSS JOIN operator to generate a Cartesian product of two tables. This operator can quickly generate a very large number of rows in the result, so it is recommended to only use it when really necessary.

In summary, the JOIN operators in T-SQL allow combining data from several tables in an efficient and convenient way, which can be very useful in many situations. It’s important to choose the right type of JOIN operator depending on your needs and your query, in order to get the desired result efficiently. Here is another more detailed tutorial on specific joins of the LEFT JOIN type.

SQL Server left join examples

Leave a Comment

Your email address will not be published. Required fields are marked *