Written by 10:18 Database development, Statements

SQL JOINs Tutorial with Examples

CodingSight - SQL JOINs Tutorial with Examples

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', 'bettestroom@gmail.com','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', 'tnkc@outlook.com','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', 'bill_1290@gmail.com','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', 'john_hompkins@mailchimp.com','878511311054');

SQL INNER JOIN

INNER JOIN statement returns records that have matching values in both tables.

SQL Inner Join Example

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;
syntax of the SQL INNER JOIN clause

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.

Left Outer Join Example

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;
syntax of the SQL LEFT JOIN clause

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.

Right Outer Join

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;
syntax of the SQL RIGHT JOIN clause

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.

Full Outer Join Example

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;
syntax of the SQL FULL OUTER JOIN clause

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.

SQL Cross Join

The syntax of the SQL CROSS JOIN is as follows:

SELECT * 
FROM tableA 
CROSS JOIN tableB;
syntax of the SQL CROSS JOIN

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.

SQL SELF JOIN Example

The syntax of the SQL self JOIN is as follows:

SELECT column_name(s)
FROM table1 T1, table1 T2
  WHERE condition;
syntax of the SQL self JOIN

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.

(Visited 428 times, 1 visits today)

Subscribe to our digest
to get SQL Server industry insides!

Close