SQL Server LEFT OUTER JOIN with WHERE clause

by
Sql server left outer join with where clause example

In this SQL Server tutorial, we will learn and understand how to use the SQL Server LEFT OUTER JOIN with the WHERE clause on tables by the query.

Recently, I got a requirement to join multiple tables and even filter records based on the condition for the resultset in the SQL Server. So, I came across the use of SQL Server LEFT OUTER JOIN statement with the WHERE condition.

So, in this SQL Server tutorial, we will go through the following examples where we have to use the SQL Server LEFT OUTER JOIN with the WHERE condition.

Here is the list of subtopics that we are going to cover:

  • How to use SQL Server Left Outer Join With Where Clause
  • Multiple Left Outer Join With Where Clause in SQL Server
  • Working with SQL Server Left Outer Join With Where Clause Group By
  • Using SQL Server Left Outer Join With Where Clause Greater Than
  • How to use SQL Server Left Outer Join With Where Clause Having Clause
  • Using SQL Server Left Outer Join With Where Clause Like
  • Using SQL Server Left Outer Join With Where Clause Year

SQL Server Left Outer Join With Where Clause

In SQL Server, the LEFT JOIN clause retrieves all records from the left table (TABLE_1) and matches records from the right table (TABLE_2).

Here is no difference between the LEFT JOIN and LEFT OUTER JOIN clauses, the only difference is an OUTER keyword in between them. If you want more details with examples, kindly look into the SQL Server LEFT JOIN vs LEFT OUTER JOIN subtopic.

EXAMPLE:

SELECT COUNTRIES.COUNTRY_NAME,
STATES.STATE_NAME
FROM COUNTRIES
LEFT OUTER JOIN STATES
ON COUNTRIES.COUNTRY_ID=STATES.COUNTRY_ID
WHERE COUNTRIES.COUNTRY_NAME='United States of America';

With the help of the LEFT OUTER JOIN in the SELECT statement, we retrieved the records of the country’s name with the state names from both tables i.e.; COUNTRIES and STATES tables.

In the WHERE condition, we have filtered the records of the country name as the United States of America so that it can bring all records of states name based on the WHERE condition for the result set.

Sql server left outer join with where clause example
Example of SQL Server LEFT OUTER JOIN with the WHERE clause

We hope that you have understood how to use the SQL Server LEFT OUTER JOIN with the WHERE condition on tables by the query.

Read: How To Update Table Using JOIN in SQL Server

Multiple Left Outer Join With Where Clause in SQL Server

Here, we will see an example of the multiple SQL Server LEFT OUTER JOIN with the WHERE clause on tables by the following query:

EXAMPLE:

SELECT CITY.CITY_NAME,
STATES.STATE_NAME,
COUNTRIES.COUNTRY_NAME
FROM CITY
LEFT OUTER JOIN COUNTRIES
ON CITY.COUNTRY_ID=COUNTRIES.COUNTRY_ID
LEFT OUTER JOIN STATES
ON COUNTRIES.COUNTRY_ID=STATES.COUNTRY_ID
WHERE COUNTRIES.COUNTRY_NAME='United Kingdom';

With the help of the LEFT OUTER JOIN, the SELECT statement retrieves all records of the city’s name, state name, and country’s name from these tables i.e.; CITY, STATES, and COUNTRIES tables.

In the WHERE condition, we have filtered the records of the country name as UNITED KINGDOM so that we can get the state name and city name in the result set.

Example of Multiple left outer join with where clause in SQL Server
Example of multiple LEFT OUTER JOIN with WHERE condition

We hope that you have understood how to use the multiple SQL Server LEFT OUTER JOIN with the WHERE condition on tables by the query.

Read: SQL Server Left Join With Count

SQL Server Left Outer Join With Where Clause Group By

Let’s see an example of the SQL Server LEFT OUTER JOIN with the WHERE and GROUP BY clauses on tables by the following query:

SELECT COUNT(CITY.CITY_NAME) AS CITY_PERSTATES,
STATES.STATE_NAME
FROM CITY
LEFT OUTER JOIN STATES
ON CITY.STATE_ID=STATES.STATE_ID
WHERE STATES.STATE_NAME='New York'
GROUP BY STATES.STATE_NAME;

With the help of the LEFT OUTER JOIN in the SELECT statement, we have retrieved and counted the city’s name based on the states name from both tables i.e.; CITY and STATES tables by using the WHERE condition.

In the WHERE condition, we have filtered the records of state names as New York so that it can give a count of cities under it in the result set.

Sql server left outer join with where clause group by
Example of SQL Server LEFT OUTER JOIN with WHERE condition and GROUP BY clause

We hope that you have understood how to use the SQL Server LEFT OUTER JOIN with the WHERE condition and GROUP BY clause on tables by the query.

Read: SQL Server Count Join Group By

SQL Server Left Outer Join With Where Clause Greater Than

In this SQL Server subtopic section, we will see an example of SQL Server LEFT OUTER JOIN with WHERE clause and GREATER THAN operator on tables by the following query:

EXAMPLE:

SELECT COUNTRIES.COUNTRY_NAME,
STATES.STATE_NAME
FROM COUNTRIES
LEFT OUTER JOIN STATES
ON COUNTRIES.COUNTRY_ID=STATES.COUNTRY_ID
WHERE COUNTRIES.AREA_IN_KM_SQUARE >50000;

With the help of the LEFT OUTER JOIN, we retrieved the records of the country’s name and state’s name from both tables i.e.; COUNTRIES and STATES tables by using the WHERE condition.

In the WHERE condition, we filter the records of the country’s name with the state names only when the country’s area in km2 is greater than 50 thousand km2 in the result set.

Sql server left outer join with where clause greater than example
Example of SQL Server Left Outer Join With Where Clause Greater Than

We hope that you have understood how to use SQL Server LEFT OUTER JOIN with WHERE GREATER THAN operator on tables by the query.

Read: SQL Server INNER JOIN Tutorial

SQL Server Left Outer Join With Where Clause Having Clause

In this SQL Server subtopics section, we will learn and understand how to use the SQL Server LEFT OUTER JOIN with WHERE condition and HAVING clause on tables in the following query:

EXAMPLE:

SELECT COUNT(STATES.STATE_NAME) AS STATES_PERCOUNTRY,
COUNTRIES.COUNTRY_NAME
FROM STATES
LEFT OUTER JOIN COUNTRIES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID
WHERE COUNTRIES.CONTIENT='North America'
GROUP BY COUNTRIES.COUNTRY_NAME
HAVING COUNT(STATES.STATE_NAME)>4;

In the LEFT OUTER JOIN example, we retrieved and counted the state’s name based on the country’s name from both tables i.e.; STATES and COUNTRIES tables by using the WHERE condition.

In the WHERE condition, we have filtered the continent name as North America so that it has a count of state names whose value is greater than 4 with country names in the result set.

Sql server left outer join with where clause having clause
Example of SQL Server LEFT OUTER JOIN with the WHERE condition and GROUP BY clause

We hope that you have understood how to use the SQL Server LEFT OUTER JOIN with the WHERE condition and HAVING clause on tables by the query.

Read: SQL Server Right Join Distinct

SQL Server Left Outer Join With Where Clause Like

Let us see an example of SQL Server LEFT OUTER JOIN with the WHERE condition and LIKE clause on tables by the following query:

EXAMPLE:

SELECT CITY.CITY_NAME,
STATES.STATE_NAME
FROM CITY
LEFT OUTER JOIN STATES
ON CITY.STATE_ID=STATES.STATE_ID
WHERE STATES.STATE_NAME LIKE '%k';

With the help of LEFT OUTER JOIN in the SELECT statement, we have retrieved the records of the city name and state names from both tables i.e.; CITY and STATES tables in the WHERE condition. In the WHERE condition, we filtered the records of the state name whose name ends with the letter k in the result set.

Sql server left outer join with where clause like example
Example of SQL Server LEFT OUTER JOIN with WHERE condition and LIKE clause

We trust that you are aware of how to use SQL Server LEFT OUTER JOIN with WHERE and LIKE clauses on the query’s tables.

SQL Server Left Outer Join With Where Clause Year

Let’s see an example of SQL Server YEAR function with LEFT OUTER JOIN and WHERE condition on tables by the following query:

EXAMPLE:

SELECT YEAR(EMPLOYEES.DATE_OF_BIRTH) AS DOB_YEAR,
EMPLOYEES.FIRST_NAME,
EMPLOYEES.LAST_NAME,
EMPLOYEE_CREDENTIALS.LOGIN_ID
FROM EMPLOYEES
LEFT OUTER JOIN EMPLOYEE_CREDENTIALS
ON EMPLOYEES.EMP_ID=EMPLOYEE_CREDENTIALS.EMP_ID
WHERE EMPLOYEE_CREDENTIALS.LOGIN_ID IS NOT NULL;

With the help of LEFT OUTER JOIN, the SELECT statement extracts the year portion value of employees’ date of birth and puts the new year value under the DOB_YEAR column by using the ALIAS keyword.

And we have also retrieved the records of the employee’s first name, last name and login id from both tables i.e.; EMPLOYEES and EMPLOYEE_CREDENTIALS tables with the WHERE condition. In the WHERE condition, we filter the records of login id by NOT NULL value from the EMPLOYEE_CREDENTIALS table in the result set.

Sql server left outer join with where clause year example
Example of SQL Server Left Outer Join With Where Clause Year

We hope you understood the subtopic “SQL Server LEFT OUTER JOIN with WHERE clause Year” by using the SQL Server LEFT OUTER JOIN with the WHERE clause and YEAR function on tables in the query.

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

We now know about the post “SQL Server LEFT OUTER JOIN with WHERE clause“ after reading this tutorial. To assist you in comprehending the idea, we explored the following subtopics in detail.

  • How to use SQL Server Left Outer Join With Where Clause
  • Multiple Left Outer Join With Where Clause in SQL Server
  • Working with SQL Server Left Outer Join With Where Clause Group By
  • Using SQL Server Left Outer Join With Where Clause Greater Than
  • How to use SQL Server Left Outer Join With Where Clause Having Clause
  • Using SQL Server Left Outer Join With Where Clause Like
  • Using SQL Server Left Outer Join With Where Clause Year