In this article, we are going to takes a close look at SQL Server JOINs. We will review all supported SQL Server JOIN types with syntax, visual illustrations, and examples.
As we all know, table data is the core of any SQL database. To use it effectively, database administrators need to extract records from several tables based on certain conditions regularly. And that’s exactly what SQL JOINs are for.
JOIN is an SQL clause used to retrieve the data from two or more tables based on logical relationships between the tables. Joins indicate how SQL Server should use data from one table to select the rows in another table.
Different Types of JOINs in SQL
SQL Server supports different types of JOINs, including INNER JOIN, SELF JOIN, CROSS JOIN, and OUTER JOIN. In fact, each join type defines the way two tables are related in a query. OUTER JOINS, in their turn, can be divided into LEFT OUTER JOINS, RIGHT OUTER JOINS, and FULL OUTER JOINS.
To better demonstrate how the JOINs work, we will create two tables.
CREATE TABLE AdventureWorks2019.dbo.users (
auid INT IDENTITY
,username VARCHAR(50) NOT NULL
,password VARCHAR(50) NOT NULL
,createdate DATETIME NOT NULL
,isActive TINYINT NOT NULL
);
CREATE TABLE AdventureWorks2019.dbo.userprofile (
apid INT NOT NULL
,auid INT NOT NULL
,firstname VARCHAR(50) NOT NULL
,lastname VARCHAR(50) NOT NULL
,email VARCHAR(100) NOT NULL
,phone VARCHAR(45) NOT NULL
);Next, we need to insert the data in the created tables.
USE AdventureWorks2019
GO
Insert into dbo.users
(auid, username,password, createdate, isActive)
values
(1,'admin','pswrd123', GETDATE(), 1);
Insert into dbo.userprofile
(apid, auid, firstname, lastname, email, phone)
values
(1,1,'Jack', 'Wolf', '[email protected]','600075764216');
Insert into dbo.users
(auid,username,password, createdate, isActive)
values
(2, 'admin1','pass506', GETDATE(), 1);
Insert into dbo.userprofile
(apid, auid, firstname, lastname, email, phone)
values
(2, 3, 'Tom', 'Collins', '[email protected]','878511311054');
Insert into dbo.users
(auid, username,password, createdate, isActive)
values
(4,'fox12','45@jgo0', GETDATE(), 1);
Insert into userprofile
(apid, auid, firstname, lastname, email, phone)
values
(4,5,'Bill', 'Fonskin', '[email protected]','450985764216');
Insert into dbo.users
(auid,username,password, createdate, isActive)
values
(6, 'lexus1267','98hnfRT6', GETDATE(), 1);
Insert into dbo.userprofile
(apid, auid, firstname, lastname, email, phone)
values
(7, 7, 'John', 'Hopkins', '[email protected]','878511311054');SQL INNER JOIN
INNER JOIN statement returns records that have matching values in both tables.
The syntax of the SQL INNER JOIN clause is as follows:
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
SQL OUTER JOINs
Contrary to INNER JOIN clauses, OUTER JOINs return not only matching records but non-matching ones as well. In case there are non-matching rows in a joined table, the NULL values will be shown for them.
There are the following two types of OUTER JOIN in SQL Server: SQL LEFT JOIN and SQL RIGHT JOIN. Let’s take a closer look at each of them.
SQL LEFT JOIN
SQL LEFT JOIN returns all records from the left table (table A) and the matching records from the right table (table B). The result is 0 records from the right side if there is no match.
The syntax of the SQL LEFT JOIN clause is as follows:
SELECT column_name(s)
FROM tableA
LEFT JOIN tableB
ON tableA.column_name = tableB.column_name;
SQL RIGHT JOIN
The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side if there is no match.
The syntax of the SQL RIGHT JOIN clause is as follows:
SELECT column_name(s)
FROM tableA
RIGHT JOIN tableB
ON tableA.column_name = tableB.column_name;
SQL FULL OUTER JOIN
The FULL OUTER JOIN returns all records when there is a match in left (table A) or right (table B) table records.
The syntax of the SQL FULL OUTER JOIN clause is as follows:
SELECT column_name(s)
FROM tableA
FULL OUTER JOIN tableB
ON tableA.column_name = tableB.column_name
WHERE condition;
SQL CROSS JOIN
SQL CROSS JOIN, also known as a cartesian JOIN, retrieves all combinations of rows from each table. In this type of JOIN, the result set is returned by multiplying each row of table A with all rows in table B if no additional condition is introduced.
To better understand CROSS JOINs, let’s take a look at the Venn diagram below.
The syntax of the SQL CROSS JOIN is as follows:
SELECT *
FROM tableA
CROSS JOIN tableB;
SQL Self JOIN
A self-join is a regular JOIN, but the table is joined with itself. This implies that each row of the table is combined with itself and with every other row of the table.
The syntax of the SQL self JOIN is as follows:
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
Conclusion
The knowledge and expertise in using SQL JOIN clauses are essential skills of any DBA or analyst. With its stellar Code Completion functionality, dbForge Studio for SQL Server can save your time and effort on writing even the most complex JOIN clauses. You don’t need to bear hundreds of column names or aliases in mind, dbForge Studio for SQL will prompt a full SQL JOIN clause. The rich functionality of the tool makes designing complex SQL queries and managing JOIN conditions quick, easy, and convenient.
Tags: inner join, outer join, sql join Last modified: October 19, 2021
