{"id":26187,"date":"2023-05-25T05:22:00","date_gmt":"2023-05-25T03:22:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=26187"},"modified":"2023-07-28T17:10:05","modified_gmt":"2023-07-28T15:10:05","slug":"sql-server-join-operator","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/t-sql\/sql-server-join-operator\/","title":{"rendered":"SQL Server queries using the JOIN Operator"},"content":{"rendered":"\n

Example of queries using the JOIN operator written in T-SQL language, the goal is to join data from multiple tables.<\/em><\/h4>\n\n\n\n

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.<\/p>\n\n\n\n

1. Create the sample tables for customers and orders with SSMS<\/h2>\n\n\n\n

Here’s how to create two example tables used in the following JOIN queries.<\/p>\n\n\n\n

Create the Customers table<\/em><\/h3>\n\n\n\n

Run this code in SSMS for example to create the customers table and insert rows into it.<\/p>\n\n\n\n

-- Create the customers table\nCREATE TABLE customers (\nid INT PRIMARY KEY,\nname VARCHAR(255),\ncity VARCHAR(255)\n);\n\n-- Insert rows into the customers table\nINSERT INTO customers\n(id, name, city)\nVALUES\n(1, 'Dupont', 'Paris'),\n(2, 'Durand', 'Lyon'),\n(3, 'Martin', 'Marseille');\n<\/pre>\n\n\n\n
Create the Orders table<\/em><\/h3>\n\n\n\n
Next, create the orders table with the T-SQL code below.<\/p>\n\n\n\n
-- Create the orders table\nCREATE TABLE orders (\nid INT PRIMARY KEY,\ncustomer_id INT,\namount DECIMAL(10, 2),\norder_date DATE\n);\n\n-- Insert rows into the orders table\nINSERT INTO orders\n(id, customer_id, amount, order_date)\nVALUES\n(1, 1, 100.00, '2022-01-01'),\n(2, 2, 50.00, '2022-01-02'),\n(3, 1, 200.00, '2022-01-03'),\n(4, 3, 75.00, '2022-01-04');\n<\/pre>\n\n\n\n
2. SQL Server Join of type INNER JOIN<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
SELECT *\nFROM customers c\nINNER JOIN orders o\nON c.id = o.customer_id;<\/pre>\n\n\n\n
The result of the query with the INNER JOIN operator will therefore include 4 rows, like this:<\/p>\n\n\n\n
id<\/th>name<\/th>city<\/th>id<\/th>customer_id<\/th>amount<\/th>order_date<\/th><\/tr>
1<\/td>Dupont<\/td>Paris<\/td>1<\/td>1<\/td>100.00<\/td>2022-01-01<\/td><\/tr>
2<\/td>Durand<\/td>Lyon<\/td>2<\/td>2<\/td>50.00<\/td>2022-01-02<\/td><\/tr>
1<\/td>Dupont<\/td>Paris<\/td>3<\/td>1<\/td>200.00<\/td>2022-01-03<\/td><\/tr>
3<\/td>Martin<\/td>Marseille<\/td>4<\/td>3<\/td>75.00<\/td>2022-01-04<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
3. T-SQL LEFT JOIN Operator<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
SELECT *\nFROM customers c\nLEFT JOIN orders o\nON c.id = o.customer_id;\n<\/pre>\n\n\n\n
The result of this SQL LEFT JOIN query written in T-SQL.<\/p>\n\n\n\n
id<\/th>name<\/th>city<\/th>id<\/th>customer_id<\/th>amount<\/th>order_date<\/th><\/tr>
1<\/td>Dupont<\/td>Paris<\/td>1<\/td>1<\/td>100.00<\/td>2022-01-01<\/td><\/tr>
2<\/td>Durand<\/td>Lyon<\/td>2<\/td>2<\/td>50.00<\/td>2022-01-02<\/td><\/tr>
1<\/td>Dupont<\/td>Paris<\/td>3<\/td>1<\/td>200.00<\/td>2022-01-03<\/td><\/tr>
3<\/td>Martin<\/td>Marseille<\/td>4<\/td>3<\/td>75.00<\/td>2022-01-04<\/td><\/tr>
4<\/td>Dupuis<\/td>Bordeaux<\/td>NULL<\/td>NULL<\/td>NULL<\/td>NULL<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
4. SQL Server RIGHT JOIN Operator<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
SELECT *\nFROM customers c\nRIGHT JOIN orders o\nON c.id = o.customer_id;\n<\/pre>\n\n\n\n
<\/p>\n\n\n\n
The result of the right join query above will give a result like this one, using our sample tables: <\/p>\n\n\n\n
id<\/th>name<\/th>city<\/th>id<\/th>customer_id<\/th>amount<\/th>order_date<\/th><\/tr>
1<\/td>Dupont<\/td>Paris<\/td>1<\/td>1<\/td>100.00<\/td>2022-01-01<\/td><\/tr>
2<\/td>Durand<\/td>Lyon<\/td>2<\/td>2<\/td>50.00<\/td>2022-01-02<\/td><\/tr>
1<\/td>Dupont<\/td>Paris<\/td>3<\/td>1<\/td>200.00<\/td>2022-01-03<\/td><\/tr>
3<\/td>Martin<\/td>Marseille<\/td>4<\/td>3<\/td>75.00<\/td>2022-01-04<\/td><\/tr>
NULL<\/td>NULL<\/td>NULL<\/td>5<\/td>2<\/td>25.00<\/td>2022-01-05<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
You may also be interested in the SQL Server data type and their management.<\/p>\n\n\n\n
\n
SQL Server data types with code examples to create columns<\/a><\/blockquote>