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).

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:


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).

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.

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.

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.

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.

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.

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.

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.

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.

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.
- Instead of Trigger In SQL Server
- SQL Server Inner Join With Where Clause
- SQL Server LEFT OUTER JOIN with WHERE clause
- SQL Server Date Format
- SQL Server Right Join Distinct
- SQL Server Count Join Group By
- SQL Server INNER JOIN Tutorial
- SQL Server SELF JOIN
- SQL Server First Day Of Month
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?
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.