SQL Server Inner Join With Where Clause

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

Recently I got a requirement where I need to join multiple tables and even filter some data in SQL Server. So, I came across the use of SQL Server INNER JOIN statement with WHERE condition.

So, in this SQL Server tutorial, we will go through the following examples where we have used the SQL Server INNER JOIN with the WHERE condition.

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

• How to use SQL Server Inner Join With Where Clause
• SQL Server Multiple Inner Join With Where Clause
• SQL Server Inner Join With Where Clause For Multiple Conditions
• Using SQL Server Inner Join With Where Clause Group By
• Using SQL Server Inner Join With Where Clause Having Clause
• How to use SQL Server Inner Join With Where Clause Like
• How to use SQL Server Inner Join with Where Clause Year

SQL Server Inner Join With Where Clause

The SQL Server INNER JOIN clause is used to select records from both tables that have matching values. Here is an example of SQL Server INNER JOIN with the WHERE condition on tables by the following query:

EXAMPLE:

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

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

Using the common column as COUNTRY_ID from both tables, we have filtered records with the continent name as North America in the WHERE condition to obtain records with state names and nation names in the result set.

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

Read: SQL Server Right Join Distinct

SQL Server Multiple Inner Join With Where Clause

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

EXAMPLE:

SELECT CITY.CITY_NAME,
STATES.STATE_NAME,
COUNTRIES.COUNTRY_NAME
FROM CITY
INNER JOIN COUNTRIES
ON CITY.COUNTRY_ID=COUNTRIES.COUNTRY_ID
INNER JOIN STATES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID
WHERE COUNTRIES.COUNTRY_NAME='United States of America';

With the help of the INNER JOIN clause, we have retrieved the city names, states names and country names from these tables i.e.; CITY, STATES and COUNTRIES table based on the WHERE condition.

In the WHERE condition, we filtered the records of the country name as UNITED STATES OF AMERICA so that we can get records of city names, states names and country names in the resultset.

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

Read: RIGHT JOIN in SQL Server

SQL Server Inner Join With Where Clause For Multiple Conditions

Let’s see an example of SQL Server INNER JOIN with multiple WHERE conditions on tables by the following query:

EXAMPLE:

SELECT CITY.CITY_NAME,
STATES.STATE_NAME
FROM CITY
INNER JOIN STATES
ON CITY.STATE_ID=STATES.STATE_ID
WHERE STATES.STATE_NAME BETWEEN 'New York'
AND 'Toronto';

With the help of the INNER JOIN in the SELECT statement, we retrieved the city names and state names from both tables i.e.; CITY and STATES tables based on the WHERE condition.

In the WHERE condition, we filtered the state names between New York and Toronto which will carry all city names in the result set.

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

Read: SQL Server Left Join With Count

SQL Server Inner Join With Where Clause Group By

Here, we will see an example of the SQL Server INNER JOIN with the WHERE condition and GROUP BY clauses on tables by the following query:

EXAMPLE:

SELECT COUNT(STATES.STATE_NAME) AS STATE_PERCOUNTRY,
COUNTRIES.COUNTRY_NAME
FROM STATES
INNER JOIN COUNTRIES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID
WHERE STATES.COUNTRY_ID=1
GROUP BY COUNTRIES.COUNTRY_NAME;

With the help of the INNER JOIN statement, we have retrieved and counted the state names based on the country names from both tables i.e.; STATES and COUNTRIES table based on the WHERE condition.

So basically the country name UNITED STATES OF AMERICA comes in the ID as 1 in the COUNTRY table. In the WHERE condition, we filtered the records which carry the country id as 1 so that the result set brings the count of state names based on the country names.

We hope you understand how to use the SQL Server INNER JOIN with WHERE condition and GROUP BY clauses on tables by the query.

Read: SQL Server Outer Join With Count

SQL Server Inner Join With Where Clause Having Clause

In this SQL Server subtopic section, we will learn and understand how to use the SQL Server INNER JOIN with WHERE and HAVING clauses on tables by the following query:

EXAMPLE:

SELECT COUNT(STATES.STATE_NAME) AS STATES_PERCOUNTRY,
COUNTRIES.COUNTRY_NAME
FROM STATES
INNER JOIN COUNTRIES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID
WHERE COUNTRIES.COUNTRY_NAME LIKE '%a'
GROUP BY COUNTRIES.COUNTRY_NAME
HAVING COUNT(STATES.STATE_NAME)>3;

With the help of the INNER JOIN in the SELECT statement, we retrieved and counted the value of states’ names with countries‘ names from both tables i.e.; STATES and COUNTRIES table for the result set.

In the WHERE condition, we have filtered the record of the country names which carry the letter ‘a’ at the ending of a variable and then bring the result set of the counted value of state names whose value is greater than 3 and also records of the country names.

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

Read: How to use SQL Server Left Join on Distinct

SQL Server Inner Join With Where Clause Like

Here we will see an example of the SQL Server INNER JOIN with WHERE and LIKE clauses on tables by the following query:

EXAMPLE:

SELECT STATES.STATE_NAME,
COUNTRIES.COUNTRY_NAME
FROM STATES
INNER JOIN COUNTRIES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID
WHERE COUNTRIES.CONTIENT LIKE 'a%';

As we see in the above query example, we have retrieved the records of the state names and country names from both tables i.e.; STATES and COUNTRIES tables which is based on the WHERE condition in the resultset. In the WHERE condition, we have filtered the continent names whose name ends with the letter ‘a‘ for the result set.

We hope that you understand how to use the SQL Server INNER JOIN with the WHERE and LIKE clauses on tables by the query.

Read: How To Update Table Using JOIN in SQL Server

SQL Server Inner Join with Where Clause Year

We will use two tables i.e.; EMPLOYEES and TIMESHEET tables for the YEAR function with the INNER JOIN clause and WHERE condition on tables by the query.

EXAMPLE:

SELECT YEAR(TIMESHEET.CHECK_IN) AS EMPLOYEES_YEARVALUE,
EMPLOYEES.FIRST_NAME
FROM TIMESHEET
INNER JOIN EMPLOYEES
ON TIMESHEET.EMP_ID=EMPLOYEES.EMP_ID;

In the INNER JOIN query example, we retrieved and extracted the year portion value of the CHECK_IN column with the employee’s first name from both tables i.e.; TIMESHEET and EMPLOYEES tables.

We hope that you understood how to use the SQL Server YEAR function with the INNER JOIN and WHERE clauses on tables by the query.

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

• MySQL vs SQL Server
• PostgreSQL vs SQL Server
• Identity Column in SQL Server
• SQL Server LEFT OUTER JOIN with WHERE clause

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 SQL Server Inner Join With Where Clause
• SQL Server Multiple Inner Join With Where Clause
• SQL Server Inner Join With Where Clause For Multiple Conditions
• Using SQL Server Inner Join With Where Clause Group By
• Using SQL Server Inner Join With Where Clause Having Clause
• How to use SQL Server Inner Join With Where Clause Like
• How to use SQL Server Inner Join with Where Clause Year