Join With Where Clause in SQL Server

In this SQL Server tutorial, we will learn how to use the join with where clause in SQL Server.

Recently, I was working on Employees Management System in the Employee Database. And I learned how to use the WHERE clause with the different JOIN types in SQL Server.

Here is the subtopic that we are going to discuss:

• How to use join with where clause in SQL Server
• Using left join with where clause in SQL Server
• How to use cross join with where clause in SQL Server
• How to use full outer join with where clause in SQL Server
• Using multiple left join with where clause in SQL Server

Here is the ER diagram of the Employee Management System and we will also let you know how ER diagram can be used for the WHERE clause with JOINS type clause in SQL Server.

Join With Where Clause in SQL Server

The SQL Server JOIN clause combines two or more linked tables based on the relevant columns between the tables. There are four types of JOIN clauses:

• INNER JOIN clause: It returns records based on matching columns from both tables.
• LEFT JOIN clause: It returns all records from the left table (TABLE_1) and matching records from the right table (TABLE_2).
• RIGHT JOIN clause: It returns all records from the right table ( TABLE_2) and matching records from the left table ( TABLE_1).
• FULL OUTER JOIN clause: It returns all records from where there is a match either in the left table (TABLE_1) or right table (TABLE_2).

The SQL Server WHERE clause filters record based on specific conditions in the result set.

Let’s see the output image of MASTER TABLE i.e; EMPLOYEES, DEPARTMENT, JOB_TITLE, and MANAGER tables:

Here is a sample example of SQL Server WHERE clause with the INNER JOIN clause on tables by the following query:

EXAMPLE:

SELECT JOB_TITLE.PROFILE,DEPARTMENT.DEPARTMENT_NAME
FROM JOB_TITLE
INNER JOIN DEPARTMENT
ON JOB_TITLE.DEPT_ID=DEPARTMENT.DEPT_ID
WHERE DEPARTMENT.DEPARTMENT_NAME='Information Technology';

With the help of the INNER JOIN clause, the SELECT statement retrieves all the names of the job titles based on the INFORMATION_TECHNOLOGY department from both tables in the resultset. Both tables have received job titles based on the WHERE condition.

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

Read: Delete From With Join in SQL Server

Left Join With Where Clause in SQL Server

Let’s see how to use SQL Server LEFT JOIN clause with the WHERE condition on tables by the following query:

EXAMPLE:

SELECT EMPLOYEES.FIRST_NAME,EMPLOYEES.LAST_NAME,
MANAGERS.MANAGER_NAME
FROM EMPLOYEES 
INNER JOIN MANAGERS 
ON EMPLOYEES.MANAGER_ID=MANAGERS.MANAGER_ID
WHERE MANAGERS.MANAGER_NAME LIKE '%a%';

With the help of the LEFT JOIN clause, the SELECT statement retrieves all employee first_name and last_name and manager_name whose name ends in between alphabet a in the result set.

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

Read:

Cross Join With Where Clause in SQL Server

If in SQL Server, no WHERE clause is used in conjunction with the SQL Server CROSS JOIN clause, then the result set is equal to the product of the rows in the first_table times the rows in the second_table. A Cartesian Product is a particular form of outcome.

SYNTAX:

SELECT SELECTED_COLUMN 
FROM TABLE_1
CROSS JOIN TABLE_2
WHERE [CONDITIONS];

EXAMPLE:

SELECT EMPLOYEES.FIRST_NAME,EMPLOYEES.LAST_NAME,
MANAGERS.MANAGER_NAME
FROM EMPLOYEES 
CROSS JOIN MANAGERS
WHERE EMPLOYEES.MANAGER_ID>2;

With the help of the CROSS JOIN clause, the SELECT statement retrieves the record on the basis of the manager_id greater than 2 and retrieves rows from the FIRST_NAME, LAST_NAME, and MANAGER_NAME columns in the result set.

We hope you understand how to use SQL Server CROSS JOIN clause with the WHERE condition on two tables in a query.

Read: SQL Server Left Join With Count

Full Outer Join With Where Clause in SQL Server

Here, we’ll demonstrate how to use the following SQL Server FULL OUTER JOIN with tables in the WHERE condition:

EXAMPLE:

SELECT EMPLOYEES.FIRST_NAME,EMPLOYEES.LAST_NAME,
JOB_TITLE.PROFILE
FROM EMPLOYEES
LEFT JOIN JOB_TITLE
ON EMPLOYEES.JOB_ID=JOB_TITLE.JOB_ID
WHERE JOB_TITLE.PROFILE LIKE '% Engineer';

Using the FULL OUTER JOIN clause, the SELECT query retrieves every instance of the employee’s first name, last name, and job titles from both tables. And it is done based on the job profile that ends with the word Engineer in the resultset’s WHERE criteria.

You now understand how to combine the WHERE condition on the query’s tables with the FULL OUTER JOIN clause.

Read: SQL Server Outer Join With Count

Multiple Left Join With Where Clause in SQL Server

In this section, we will see how to use several times SQL Server LEFT JOIN clause with the WHERE condition on several tables by the following query:

EXAMPLE:

SELECT DEPARTMENT.DEPARTMENT_NAME,JOB_TITLE.PROFILE,
MANAGERS.MANAGER_NAME
FROM DEPARTMENT
LEFT JOIN JOB_TITLE
ON DEPARTMENT.DEPT_ID=JOB_TITLE.DEPT_ID
LEFT JOIN MANAGERS
ON DEPARTMENT.DEPT_ID=MANAGERS.DEPT_ID
WHERE DEPARTMENT.DEPARTMENT_NAME='Information Technology';

The SELECT statement, therefore, retrieves department name, profile, and job title based on the WHERE condition from all three tables, i.e., DEPARTMENT, JOB TITLE, and MANAGERS tables, based on the LEFT JOIN clause.

We have just filtered the result set using the department_name as Information Technology in the WHERE condition.

We hope you understand how to use SQL Server LEFT JOIN clause with the WHERE condition on multiple tables in the query.

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

• SQL Server SELF JOIN 
• SQL Server OUTER JOIN
• RIGHT JOIN in SQL Server
• SQL Server Cross Join vs Left Outer Join

We now know about the post “Join With Where Clause SQL Server“ after reading this tutorial. To assist you in comprehending the idea, we explored the following subtopics in detail.

• How to use join with where clause in SQL Server
• Using left join with where clause in SQL Server
• How to use cross join with where clause in SQL Server
• How to use full outer join with where clause in SQL Server
• Using multiple left join with where clause in SQL Server