EN
MS SQL Server - Full Outer Join
0 points
In this article, we would like to show you how to do FULL OUTER JOIN in MS SQL Server.
Quick solution:
xxxxxxxxxx1
SELECT * FROM [table1]2
FULL JOIN [table2] ON [table2].[column_name] = [table1].[column_name]Note:
You might as well write
FULL JOINinstead ofFULL OUTER JOIN.
To show how the FULL OUTER 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 information about users and departments.
Query:
xxxxxxxxxx1
SELECT * 2
FROM [users] 3
FULL JOIN [departments] ON [departments].[id] = [users].[department_id]Output:
create_tables.sql file:
xxxxxxxxxx1
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:
xxxxxxxxxx1
INSERT INTO [departments]2
([id], [department_name], [location])3
VALUES4
(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
VALUES13
('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);