SQL Server FULL OUTER JOIN with WHERE clause

In this SQL Server tutorial, we will learn and understand how to use the SQL Server FULL OUTER JOIN with 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 FULL 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 FULL OUTER JOIN with the WHERE condition.

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

• How to use SQL Server Full Outer Join With Where Clause
• Using SQL Server Full Outer Join With Where Clause and Group By
• How to use SQL Server Full Outer Join With Where Clause Count
• How to use SQL Server Full Outer Join With Where Clause Greater Than
• Using SQL Server Full Outer Join With Where Clause Having Clause
• Working of SQL Server Full Outer Join With Where Clause Like
• How to use SQL Server Full Outer Join With Where Clause Year

SQL Server Full Outer Join With Where Clause

The SQL Server FULL OUTER JOIN clause is used to return all records when there is a match in left_table (TABLE_1) or right_table (TABLE_2) records.

Let’s see an example of SQL Server FULL OUTER JOIN with the WHERE clause on tables by the following query:

SELECT COUNTRIES.COUNTRY_NAME,
STATES.STATE_NAME
FROM COUNTRIES
FULL OUTER JOIN STATES
ON COUNTRIES.COUNTRY_ID=STATES.COUNTRY_ID
WHERE COUNTRIES.CONTIENT='North America';

With the help of the FULL OUTER JOIN, the SELECT statement returns all records of the country’s name with the state names from both tables i.e.; COUNTRIES and STATES by using the WHERE condition.

In the WHERE condition, we filtered the records of the continent name as North America so that it retrieves all records of country name with the state name in the result set.

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

Read: SQL Server SELF JOIN 

SQL Server Full Outer Join With Where Clause and Group By

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

EXAMPLE:

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

With the help of the FULL OUTER JOIN in the SELECT statement, the SELECT statement retrieves and counts all state’s names based on the country name from both tables i.e.; STATES and COUNTRIES tables by the WHERE condition and GROUP BY clauses.

In the WHERE condition, we filtered the records of the continent name as North America so that it can count the state name based on the country name and gives the name of the country in the result set.

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

Read: SQL Server Right Join Distinct

SQL Server Full Outer Join With Where Clause Count

If we want to use aggregate functions like (MAX, MIN, SUM, AVG, etc) then we need to use the GROUP BY clause in the query. However, we can’t use clauses like (DISTINCT) with the GROUP BY clause without using any aggregate function.

Let us see an example of SQL Server FULL OUTER JOIN with the WHERE clause and no aggregate function:

SELECT COUNT(STATES.STATE_NAME) AS STATES_PERCOUNTRY,
COUNTRIES.COUNTRY_NAME
FROM STATES
FULL OUTER JOIN COUNTRIES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID
WHERE COUNTRIES.CONTIENT='Australia';

So, in this query without using the GROUP BY clause, we got a syntax error related to aggregate functions. And to count the value of state names based on the country name, we have to use the GROUP BY clause on both tables i.e.; STATES and COUNTRIES tables.

EXAMPLE:

SELECT COUNT(STATES.STATE_NAME) AS STATES_PERCOUNTRY,
COUNTRIES.COUNTRY_NAME
FROM STATES
FULL OUTER JOIN COUNTRIES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID
WHERE COUNTRIES.CONTIENT='Australia'
GROUP BY COUNTRIES.COUNTRY_NAME;

In the FULL OUTER JOIN query example, we retrieved and counted the state names based on the country name from both tables i.e.; STATES and COUNTRIES. In the WHERE condition, we filtered the records of the continent name as Australia so that it can count the state names based on Australia Country in the result set.

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

Read: SQL Server LEFT JOIN Tutorial

SQL Server Full Outer Join With Where Clause Greater Than

Let’s see an example of SQL Server FULL OUTER JOIN with the WHERE clause and GREATER THAN operator on tables by the following query:

EXAMPLE:

SELECT COUNTRIES.COUNTRY_NAME,
STATES.STATE_NAME
FROM COUNTRIES
FULL OUTER JOIN STATES
ON COUNTRIES.COUNTRY_ID=STATES.COUNTRY_ID
WHERE COUNTRIES.TOTAL_POPULATION >50000000;

With the help of the FULL OUTER JOIN in the SELECT statement, we have filtered the records of country names and state names from these tables i.e.; COUNTRIES and STATES by using the WHERE condition.

In the WHERE condition, we have filtered the total population by 50 million from the COUNTRIES table so that it can bring all records of the country’s name and state’s name which are more than the population value in the result set.

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

Read: What is SQL Server Cross Join?

SQL Server Full Outer Join With Where Clause Having Clause

In this SQL Server subtopic section, we will learn and understand how to use the FULL 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
FULL 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)> 6;

With the help of the FULL OUTER JOIN in the SELECT statement, 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 and GROUP BY HAVING clause.

In the WHERE condition, we filtered the continent name as North America so that the state names which carry values greater than 6 were counted and the country’s name retrieved.

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

Read: SQL Server Left Join With Count

SQL Server Full Outer Join With Where Clause Like

Let’s see an example of SQL Server FULL OUTER JOIN with the WHERE condition and LIKE clause on tables in the following query:

EXAMPLE:

SELECT CITY.CITY_NAME,
STATES.STATE_NAME
FROM CITY
FULL OUTER JOIN STATES
ON CITY.STATE_ID=STATES.STATE_ID
WHERE STATES.STATE_NAME LIKE '%a%'
AND CITY.CITY_NAME IS NOT NULL;

In the FULL OUTER JOIN query example, the SELECT statement retrieves all records of the city’s name and state’s name from both tables i.e.; CITY and STATES by using the WHERE condition.

And to get the result set, we filtered the record of the state’s name whose names start in between the letter a and also brings NOT NULL value from the CITY table.

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

Read: SQL Server Outer Join With Count

SQL Server Full Outer Join With Where Clause Year

Here we will see an example of SQL Server YEAR function with FULL OUTER JOIN and WHERE condition on tables by the following query:

EXAMPLE:

SELECT YEAR(TIMESHEET.CHECK_OUT) AS EMPLOYEES_CHECKOUT_TIME,
EMPLOYEES.FIRST_NAME
FROM TIMESHEET
FULL OUTER JOIN EMPLOYEES
ON TIMESHEET.EMP_ID=EMPLOYEES.EMP_ID
WHERE EMPLOYEES.GENDER='Male'
AND YEAR(TIMESHEET.CHECK_OUT) IS NOT NULL;

With the help of FULL OUTER JOIN in the SELECT statement, we extracted the year portion value from the check_out column and retrieved the employee’s first name from both tables i.e.; TIMESHEET and EMPLOYEES by using the WHERE condition.

In the WHERE condition, we filter the employee’s gender by Male and have NOT NULL value from the YEAR function in the result set.

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

Also, take a look at some more SQL Server tutorials.

• SQL Server Select a Join Without Join
• How to use SQL Server Left Join on Distinct
• How To Update Table Using JOIN in SQL Server

We now know about the post “SQL Server FULL 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 Full Outer Join With Where Clause
• Using SQL Server Full Outer Join With Where Clause and Group By
• How to use SQL Server Full Outer Join With Where Clause Count
• How to use SQL Server Full Outer Join With Where Clause Greater Than
• Using SQL Server Full Outer Join With Where Clause Having Clause
• Working of SQL Server Full Outer Join With Where Clause Like
• How to use SQL Server Full Outer Join With Where Clause Year