EN
MS SQL Server - Left Join
0 points
In this article, we would like to show you how to use LEFT JOIN in MS SQL Server.
Quick solution:
xxxxxxxxxx
1
SELECT [column1], [column2], [columnN]
2
FROM [table1]
3
LEFT JOIN [table2]
4
ON [table1].[column_name] = [table2].[column_name];
To show how the LEFT JOIN
works, we will use the following tables:
Note:
At the end of this article you can find databases preparation SQL queries.
In this example, we will select all users with department information.
Query:
xxxxxxxxxx
1
SELECT * FROM [users]
2
LEFT JOIN [departments]
3
ON [departments].[id] = [users].[department_id];
Output:
create_tables.sql
file:
xxxxxxxxxx
1
CREATE TABLE [departments] (
2
[id] INT NOT NULL,
3
[department_name] VARCHAR(50) NOT NULL,
4
[location] VARCHAR(50) NULL,
5
PRIMARY KEY ([id])
6
);
7
8
CREATE TABLE [users] (
9
[id] INT IDENTITY(1,1),
10
[name] VARCHAR(50) NOT NULL,
11
[surname] VARCHAR(50) NOT NULL,
12
[department_id] INT,
13
PRIMARY KEY ([id]),
14
FOREIGN KEY ([department_id]) REFERENCES [departments] ([id])
15
);
insert_data.sql
file:
xxxxxxxxxx
1
INSERT INTO [departments]
2
([id], [department_name], [location])
3
VALUES
4
(1, 'Sales', 'New York'),
5
(2, 'Finance', NULL),
6
(3, 'HR', 'Atlanta'),
7
(4, 'Purchase', 'New Orlean'),
8
(5, 'Operations', 'Boston');
9
10
INSERT INTO [users]
11
( [name], [surname], [department_id])
12
VALUES
13
('John', 'Stewart', 1),
14
('Chris', 'Brown', 2),
15
('Kate', 'Lewis', 3),
16
('Ailisa', 'Gomez', NULL),
17
('Gwendolyn', 'James', 2),
18
('Simon', 'Collins', 4),
19
('Taylor', 'Martin', 2),
20
('Andrew', 'Thompson', NULL);