Which type of join will show all records from one table in a query even if those records have no matching records in the joined table?

Types of SQL JOINS Explained with Examples

JOINS fundamentals

In relational databases, such as SQL Server, Oracle, MySQL, and others, data is stored in multiple tables that are related to each other with a common key value. Accordingly, there is a constant need to extract records from two or more tables into a results table based on some condition. In SQL Server, this can be easily accomplished with the SQL JOIN clause.

JOIN is an SQL clause used to query and access data from multiple tables, based on logical relationships between those tables.

In other words, JOINS indicate how SQL Server should use data from one table to select the rows from another table.

Different types of JOINS in SQL Server

• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• SELF JOIN
• CROSS JOIN

Enjoying JOINS with SQL Complete

SQL Server JOINS are vitally important to master. As you progress from a database beginner to a more advanced user, you’ll continually need to fetch and combine data from more than one table. At this point, SQL Complete comes to the aid. Its code completion works well even for complex JOIN statements. You don’t need to memorize multiple column names or aliases, dbForge SQL Complete will suggest a full JOIN clause based on foreign keys, or conditions based on column names. These suggestions are available after the JOIN and ON keywords.

More than that, SQL Complete can prompt a complete SQL JOIN statement when you combine tables based on foreign keys. You can select a JOIN statement from the prompt list manually, in case you need a specific JOIN operation.

As part of our SQL JOIN tutorial, let’s have a look at different MSSQL JOIN statements types with the help of the
SQL Complete tool.

Basic SQL JOIN types

SQL Server supports many kinds of different 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 can further be divided into LEFT OUTER JOINS, RIGHT OUTER JOINS, and FULL OUTER JOINS.

• SQL INNER JOIN creates a result table by combining rows that have matching values in two or more tables.
• SQL LEFT OUTER JOIN includes in a result table unmatched rows from the table that is specified before the LEFT OUTER JOIN clause.
• SQL RIGHT OUTER JOIN creates a result table and includes into it all the records from the right table and only matching rows from the left table.
• SQL SELF JOIN joins the table to itself and allows comparing rows within the same table.
• SQL CROSS JOIN creates a result table containing paired combination of each row of the first table with each row of the second table.

Enjoy even the most complex JOINs with SQL Complete

INNER JOIN

INNER JOIN statement returns only those records or rows that have matching values and is used to retrieve data that appears in both tables.

In our example, we want to extract data from the Sales.SalesOrderDetail and Production.Product tables that are aliased with SOD for Sales.SalesOrderDetail and P for Production.Product. In the JOIN statement, we match records in those columns. Make notice, how code suggestions work in SQL Complete.

OUTER JOIN

When applying an SQL INNER JOIN, the output returns only matching rows from the stated tables. In contrast, if you use an SQL OUTER JOIN, it will retrieve not only the matching rows but also the unmatched rows as well.

The FULL OUTER JOIN returns a result that includes rows from both left and right tables. In case, no matching rows exist for the row in the left table, the columns of the right table will have nulls. Correspondingly, the column of the left table will have nulls if there are no matching rows for the row in the right table.

LEFT OUTER JOIN

The LEFT OUTER JOIN gives the output of the matching rows between both tables. In case, no records match from the left table, it shows those records with null values.

In our example, we want to join the tables Person.Person and HumanResources.Employee to retrieve a list of all Person LastNames, but also show JobTitle if the Person is an Employee.

In the output, in case, there are no employees matching BusinessEntityID, NULL values will be listed in the corresponding rows for NationalIDNumber and JobTitle.

RIGHT OUTER JOIN

The RIGHT OUTER JOIN works by the same principle as the LEFT OUTER JOIN. The RIGHT OUTER JOIN selects data from the right table (Table B) and matches this data with the rows from the left table (Table A). The RIGHT JOIN returns a result set that includes all rows in the right table, whether or not they have matching rows from the left table. In case, a row in the right table does not have any matching rows in the left table, the column of the left table in the result set will have nulls.

SELF JOIN

The SELF JOIN allows you to join a table to itself. This implies that each row of the table is combined with itself and with every other row of the table. The SELF JOIN can be viewed as a join of two copies of the same table. The table is not actually copied, but SQL performs the command as though it were. This is accomplished by using table name aliases to give each instance of the table a separate name. It is most useful for extracting hierarchical data or comparing rows within the same table.

In our example, we want to retrieve a list of all the territories and the salespeople working in them from the Sales.SalesPerson table.

CROSS JOIN

The CROSS JOIN command in SQL, also known as a cartesian join, returns all combinations of rows from each table. Envision that you need to find all combinations of size and color. In that case, a CROSS JOIN will be an asset. Note, that this join does not need any condition to join two tables. In fact, CROSS JOIN joins every row from the first table with every row from the second table and its result comprises all combinations of records in two tables.

dbForge SQL Complete

Enjoy even the most complex JOINs with SQL Complete

See how to use JOINs in the SELECT statements

SQL Joins let you to fetch and combine data from more than one table. In this video, we are going to demonstrate different MS SQL JOIN statements types and examples how to use JOINs in the SELECT statements. Moreover, you will see how dbForge SQL Complete helps in JOINS statements creation by prompting ready-made phrases and join conditions.

With SQL Complete, you GET

• Suggestions for full joining conditions after the JOIN keyword
• Suggestions for joining conditions after the ON keyword
• Context-based prompts for all possible combinations to join tables

• Significant time savings
• An opportunity to improve your code quality
• Freedom from the need to memorize table and column names

FAQ

Joining in SQL means retrieving data from two or more than two tables based on a common field. In other words, JOINs combine data from multiple tables in a result table based on a related column between those tables.

The purpose of JOINs in SQL is to access data from multiple tables based on logical relationships between them. JOINS are used to fetch data from database tables and represent the result dataset as a separate table.

There are four main types of JOINs in SQL: INNER JOIN, OUTER JOIN, CROSS JOIN, and SELF JOIN. However, remember that OUTER JOINS have two subtypes: LEFT OUTER JOIN and RIGHT OUTER JOIN. Some experts separate also a FULL OUTER JOIN.

INNER JOIN
Suppose, we have two tables: Customers (with CustomerID, Name, and CountryID columns) and Orders (with OrderID, CustomerID, and OrderDate columns). Using the INNER JOIN we can create a result table containing the CustomerID, Name, OrderID, and OrderDate columns. In such a simple way, we will be able to see who and when created an order. The tables will be matched based on the common CustomerID column, and the unmatched row won't get into the result dataset.

LEFT OUTER JOIN
Suppose we have the Products and Orders tables that have a common ProductId column. With the help of the LEFT OUTER JOIN we can combine the two tables in a result table that will contain all the rows from the Products table and in case, no records match from the Orders table, the NULL values will be shown in the OrderID column. In such a simple way, we will be able to see which products have not been ordered yet.

RIGHT OUTER JOIN
The RIGHT OUTER JOIN works similarly to the LEFT OUTER JOIN but in the opposite direction. Let's consider the Vendors and Orders tables. Using the RIGHT OUTER JOIN we can retrieve records for all Vendors showing their Purchase Orders. In case a vendor hasn't placed any orders yet, NULLs will be displayed in the PurchaseOrderId column.

SQL Complete

Advanced solution for SQL database development, management, and administration

Which type of join will show all records from one table in a query?

Which join type will show a record from both tables if both tables have a matching record?

Which type of join is used to returns all records when there is a match in either left or right table?

Which type of join will return rows with a match in one of the tables?