SQL Server LEFT JOIN Tutorial

by
Sql server left join

In the SQL Server tutorial, we will learn and understand how to use the SQL Server LEFT JOIN clause on both tables by the query. The following is a list of the subtopics that will be covered:

  • Define LEFT JOIN in SQL Server.
  • What difference between the SQL Server LEFT JOIN and LEFT OUTER JOIN clauses?
  • How to use SQL Server TOP 1 with the LEFT JOIN clause?
  • Without the ON clause, how can we use the LEFT JOIN condition in SQL Server?
  • How can we utilize a SQL Server LEFT JOIN on tables with numerous columns?
  • What distinguishes the LEFT JOIN and INNER JOIN clauses in SQL Server?
  • How to use SQL Server LEFT JOIN clause with the WHERE condition?
  • How to find the first match from SQL Server LEFT JOIN clause?

SQL Server LEFT JOIN

Let us say, we have two tables, Table_1 and Table_2. In the case of the LEFT JOIN, it will get all the records from the left table (Table_1) and the matching records from the right table (Table_2). If no matching records are found, then it will get all the records from the left table (Table_1).

Sql server left join
Example of SQL Server LEFT JOIN

Syntax:

SELECT  SELECTED_COLUMNLIST
FROM TABLE_1
LEFT JOIN TABLE_2
ON TABLE_1.COMMON_COLUMN= TABLE_2.COMMON_COLUMN
WHERE [ CONDITIONS ];

In the syntax explanation:

  • TABLE_1 and TABLE_2 are the left or right tables, respectively.
    • For each row from the TABLE_1 table, the query compares all rows from the right table TABLE_2.
    • The column values from two rows that cause the join predicate to evaluate to TRUE are joined to create a new row, which is then added to the result set.
  • In the end, we have used the WHERE condition which is optional. And it retrieves data based on the filter of the result set.

Here we have used the DRUG_COMPANY and COMPANY_OWNER tables for LEFT JOIN:

How to use SQL Server left join
DRUG_COMPANY table
Example of Sql server left join image
DRUG_COMPANY table

EXAMPLE:


SELECT COMPANY_OWNER.OWNER_NAME,
DRUG_COMPANY.COMPANY_NAME
FROM COMPANY_OWNER
LEFT JOIN DRUG_COMPANY
ON COMPANY_OWNER.COMPANY_ID=DRUG_COMPANY.COMPANY_ID;

With the help of the LEFT JOIN clause, it retrieves all records from the left_table (COMPANY_OWNER) and matching records from the right_table (DRUG_COMPANY).

Sql server left join query example
Example of SQL Serve LEFT JOIN clause

This is how to use the SQL Server LEFT JOIN clause on these tables by the query.

Read: SQL Server Outer Join With Count

SQL Server Left Join Vs Left Outer Join

There is no difference between SQL Server LEFT JOIN and LEFT OUTER JOIN clause. The definition and syntax of both are the same. The only difference is that the OUTER keyword is used between SQL Server LEFT JOIN clause.

And it is also put no difference between the SQL Server RIGHT JOIN and RIGHT OUTER JOIN clause.

LEFT OUTER JOIN Clause Example:

SELECT COMPANY_OWNER.OWNER_NAME,
DRUG_COMPANY.COMPANY_NAME
FROM COMPANY_OWNER
LEFT OUTER JOIN DRUG_COMPANY
ON COMPANY_OWNER.COMPANY_ID=DRUG_COMPANY.COMPANY_ID;

With the help of LEFT OUTER JOIN, it retrieves all records from the COMPANY_OWNER and the matching records from the DRUG_COMPANY table. Both reflect the same query output while using the LEFT JOIN or LEFT OUTER JOIN clause.

If you want to see a query example of the LEFT JOIN, then look into the subtopic “SQL Server LEFT JOIN”. The main point is that the OUTER keyword is optional if we wish to use the LEFT JOIN. You have a choice in the query.

Sql server left join vs left outer join
Example of SQL Server LEFT OUTER JOIN clause

We hope that you have understood that there is no difference between the SQL Server LEFT JOIN and SQL Server LEFT OUTER JOIN clauses.

Read: Recursive Trigger in SQL Server

SQL Server Left Join With Top 1

The SELECT TOP statement allows us to limit the number of records in the query for the result set.

EXAMPLE:


SELECT TOP 1 COMPANY_OWNER.OWNER_NAME,
DRUG_COMPANY.COMPANY_NAME
FROM COMPANY_OWNER
LEFT OUTER JOIN DRUG_COMPANY
ON COMPANY_OWNER.COMPANY_ID=DRUG_COMPANY.COMPANY_ID;

The TOP 1 term, which we used in the beginning, indicates that the first record from the LEFT JOIN clause query on these tables would be returned for the result set.

Sql server left join with top 1
Example of SQL Server LEFT JOIN with Top 1

We hope that how you understood how to use the SQL Server SELECT TOP statement with the LEFT JOIN clause on multiple tables by the query.

Read: Debug an SQL Server Trigger

SQL Server Left Join Without On

According to how we understand this subtopic, you want to have more records from the LEFT TABLE than some records from the RIGHT TABLE while still looking for something you are familiar with using the CROSS JOIN clause.

But this can’t be possible if we don’t use the ON clause in SQL Server LEFT JOIN clause.

EXAMPLE:


SELECT COMPANY_OWNER.OWNER_NAME,
DRUG_COMPANY.COMPANY_NAME
FROM COMPANY_OWNER
LEFT OUTER JOIN DRUG_COMPANY;

So, when we don’t use the ON condition in the LEFT JOIN clause for both tables, it gives a syntax error.

Sql server left join without on tutorial
Error Example of SQL Server LEFT JOIN clause without ON condition

LEFT JOIN clause with ON condition Example:


SELECT COMPANY_OWNER.OWNER_NAME,
DRUG_COMPANY.COMPANY_NAME
FROM COMPANY_OWNER
LEFT OUTER JOIN DRUG_COMPANY
ON COMPANY_OWNER.COMPANY_ID=DRUG_COMPANY.COMPANY_ID;

The SELECT statement fetches all records of the selected_column from both tables by using the LEFT OUTER JOIN condition for the result set.

Example of Sql server left join without on
Example of SQL Server LEFT JOIN with the ON condition

We trust you now understand that the ON clause is necessary for both tables to bring the result set for the SQL Server LEFT JOIN condition to work.

Read: SQL Server Trigger On View

SQL Server Left Join on Multiple Columns

Let’s use the SQL Server LEFT JOIN clause on multiple columns of tables by the following query example.


SELECT COMPANY_OWNER.*,
DRUG_COMPANY.COMPANY_NAME
FROM COMPANY_OWNER
LEFT OUTER JOIN DRUG_COMPANY
ON COMPANY_OWNER.COMPANY_ID=DRUG_COMPANY.COMPANY_ID;

With the help of the LEFT OUTER JOIN clause, the SELECT statement retrieves all records from the COMPANY_OWNER and with matching records from the DRUG_COMPANY table.

Sql server left join on multiple columns example
Example of SQL Server LEFT JOIN on multiple columns of tables

So, you have understood how to use the SQL Server LEFT JOIN clause on multiple columns of tables by the query.

Read: SQL Server Trigger Before Update

SQL Server Left Join Vs Inner Join

According to the definition of the LEFT JOIN condition in SQL Server, it is a clause of the SELECT statement and is used to query data from many tables. All rows from the left table (table_1) are combined with matched rows from the right table (table_2) by the SQL Server LEFT JOIN clause.

If you want to see how the SQL Server LEFT JOIN clause works then go under this subtopic “SQL Server LEFT JOIN” for understanding it with an example.

SQL Server INNER JOIN clause:

It is commonly used to join in SQL Server. And the INNER JOIN clause allows you to retrieve data from two or more two related tables.

Sql server left join vs inner join tutorial
Example of SQL Server INNER JOIN

Let’s see an example of the SQL Server INNER JOIN clause on tables by the following query:


SELECT COMPANY_OWNER.OWNER_NAME,
DRUG_COMPANY.COMPANY_NAME
FROM COMPANY_OWNER
INNER JOIN DRUG_COMPANY
ON COMPANY_OWNER.COMPANY_ID=DRUG_COMPANY.COMPANY_ID;

With the help of the INNER JOIN clause, the SELECT statement selects records that have matching values from both tables.

Sql server left join vs inner join
Example of SQL Server INNER JOIN clause

We hope that you have understood the difference between the SQL Server LEFT JOIN and INNER JOIN clauses.

Read: Trigger For Delete in SQL Server

SQL Server Left Join With Where Clause

The SQL Server WHERE condition is used to extract those records which fulfil their condition. Apart from the SELECT statement, it can also use with the UPDATE and DELETE statements.

EXAMPLE:


SELECT COMPANY_OWNER.OWNER_NAME,
DRUG_COMPANY.COMPANY_NAME
FROM COMPANY_OWNER
INNER JOIN DRUG_COMPANY
ON COMPANY_OWNER.COMPANY_ID=DRUG_COMPANY.COMPANY_ID
WHERE COMPANY_OWNER.COMPANY_ID>4;

So with the help of the LEFT JOIN clause, the SELECT statement retrieve all records from the COMPANY_OWNER and with matching records from the DRUG_COMPANY table which is used with the WHERE condition.

The COMPANY_ID column of the resultset is searched for all records greater than 4 using the WHERE condition.

Sql server left join with where clause
Example of SQL Server LEFT JOIN clause with the WHERE condition

You now have understood how to use SQL Server LEFT JOIN clause with the WHERE condition on tables by the query.

You may also like to read the following SQL Server tutorials.

After reading this tutorial, we now know about the topic “LEFT JOIN in SQL Server.” To assist you to understand the concept, here is a list of every subtopic that we have discussed.

  • Define LEFT JOIN in SQL Server.
  • What difference between the SQL Server LEFT JOIN and LEFT OUTER JOIN clauses?
  • How to use SQL Server TOP 1 with the LEFT JOIN clause?
  • Without the ON clause, how can we use the LEFT JOIN condition in SQL Server?
  • How can we utilize a SQL Server LEFT JOIN on tables with numerous columns?
  • What distinguishes the LEFT JOIN and INNER JOIN clauses in SQL Server?
  • How to use SQL Server LEFT JOIN clause with the WHERE condition?
  • How to find the first match from SQL Server LEFT JOIN clause?