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:
| id | name | city | id | customer_id | amount | order_date |
|---|---|---|---|---|---|---|
| 1 | Dupont | Paris | 1 | 1 | 100.00 | 2022-01-01 |
| 2 | Durand | Lyon | 2 | 2 | 50.00 | 2022-01-02 |
| 1 | Dupont | Paris | 3 | 1 | 200.00 | 2022-01-03 |
| 3 | Martin | Marseille | 4 | 3 | 75.00 | 2022-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.
| id | name | city | id | customer_id | amount | order_date |
|---|---|---|---|---|---|---|
| 1 | Dupont | Paris | 1 | 1 | 100.00 | 2022-01-01 |
| 2 | Durand | Lyon | 2 | 2 | 50.00 | 2022-01-02 |
| 1 | Dupont | Paris | 3 | 1 | 200.00 | 2022-01-03 |
| 3 | Martin | Marseille | 4 | 3 | 75.00 | 2022-01-04 |
| 4 | Dupuis | Bordeaux | NULL | NULL | NULL | NULL |
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:
| id | name | city | id | customer_id | amount | order_date |
|---|---|---|---|---|---|---|
| 1 | Dupont | Paris | 1 | 1 | 100.00 | 2022-01-01 |
| 2 | Durand | Lyon | 2 | 2 | 50.00 | 2022-01-02 |
| 1 | Dupont | Paris | 3 | 1 | 200.00 | 2022-01-03 |
| 3 | Martin | Marseille | 4 | 3 | 75.00 | 2022-01-04 |
| NULL | NULL | NULL | 5 | 2 | 25.00 | 2022-01-05 |
You may also be interested in the SQL Server data type and their management.
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.
| id | name | city | id | customer_id | amount | order_date |
|---|---|---|---|---|---|---|
| 1 | Dupont | Paris | 1 | 1 | 100.00 | 2022-01-01 |
| 1 | Dupont | Paris | 2 | 2 | 50.00 | 2022-01-02 |
| 1 | Dupont | Paris | 3 | 1 | 200.00 | 2022-01-03 |
| 1 | Dupont | Paris | 4 | 3 | 75.00 | 2022-01-04 |
| 2 | Durand | Lyon | 1 | 1 | 100.00 | 2022-01-01 |
| 2 | Durand | Lyon | 2 | 2 | 50.00 | 2022-01-02 |
| 2 | Durand | Lyon | 3 | 1 | 200.00 | 2022-01-03 |
| 2 | Durand | Lyon | 4 | 3 | 75.00 | 2022-01-04 |
| 3 | Martin | Marseille | 1 | 1 | 100.00 | 2022-01-01 |
| 3 | Martin | Marseille | 2 | 2 | 50.00 | 2022-01-02 |
| 3 | Martin | Marseille | 3 | 1 | 200.00 | 2022-01-03 |
| 3 | Martin | Marseille | 4 | 3 | 75.00 | 2022-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.
| id | name | city | id | customer_id | amount | order_date |
|---|---|---|---|---|---|---|
| 1 | Dupont | Paris | 1 | 1 | 100.00 | 2022-01-01 |
| 2 | Durand | Lyon | 2 | 2 | 50.00 | 2022-01-02 |
| 1 | Dupont | Paris | 3 | 1 | 200.00 | 2022-01-03 |
| 3 | Martin | Marseille | 4 | 3 | 75.00 | 2022-01-04 |
| 4 | Dupuis | Bordeaux | NULL | NULL | NULL | NULL |
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.
| name | name |
|---|---|
| Dupont | Martin |
| Martin | Dupont |
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.
Be the first to comment