SQL Server Inner Join with Like

by
Sql server inner join with like clause example

In this SQL Server tutorial, we will learn and understand how to use the SQL Server INNER JOIN with the LIKE 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 SQL Server. So, I came across the use of SQL Server INNER JOIN statement with the LIKE condition.

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

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

  • How to use SQL Server Inner Join With Like
  • Using SQL Server Inner Join with Like and Group By
  • How to use SQL Server Inner Join with Like and Having
  • SQL Server Inner Join with Like Multiple Columns
  • How to use SQL Server Inner Join With Like Not Null
  • Using SQL Server Inner Join With Like and Row_Number

    SQL Server Inner Join With Like

    In SQL Server, the INNER JOIN clause is used to select records that have matching values from both tables. Let’s see an example of SQL Server INNER JOIN with the LIKE clause on tables by the following query:

    EXAMPLE:

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

    With the help of the INNER JOIN clause, the SELECT statement retrieves all records of the city’s name based on the country’s name from both tables i.e.; CITY and COUNTRIES by the WHERE clause.

    In the WHERE condition, we filtered the records of a country name that begins with the letter U so that we can get city names based on this country in the result set.

    Sql server inner join with like clause example
    Example of SQL Server INNER JOIN with LIKE clause

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

    Read: MySQL vs SQL Server

    SQL Server Inner Join with Like Clause Group By

    Here we will see an example of SQL Server INNER JOIN with LIKE and GROUP BY 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 'U%'
GROUP BY COUNTRIES.COUNTRY_NAME;

    With the help of the INNER 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 by using the WHERE condition.

    In the WHERE condition, we filtered the country’s name whose name starts with the letter U so that we can get the count of the state’s name per country in the resultset.

    Sql server inner join with like clause group by example
    Example of SQL Server INNER JOIN with LIKE and GROUP BY clauses

    We hope that you have understood how to use the SQL Server INNER JOIN with LIKE and GROUP BY clauses on tables by the query.

    Read: PostgreSQL vs SQL Server

    SQL Server Inner Join with Like Clause Having

    In this SQL Server subtopic section, we will learn and understand how to use the INNER JOIN with LIKE 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 'U%'
GROUP BY COUNTRIES.COUNTRY_NAME
HAVING COUNT(STATES.STATE_NAME) >5;

    With the help of the INNER JOIN, we retrieved and counted the state’s name based on the country’s name from both tables i.e.; STATES and COUNTRIES by the WHERE condition.

    In the WHERE condition, we filtered the records of the country’s name whose name starts with the letter U and then counted the state name value only if it’s greater than 5 for the country’s name in the result set.

    With this query, it will bring the country name as the United States of America and the value of the total state as 10 in the output resultset.

    Sql server inner join with like clause having example
    Example of SQL Server INNER JOIN with LIKE and HAVING clauses

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

    Read: SQL Server OUTER JOIN

    SQL Server Inner Join with Like Clause Multiple Columns

    Let’s see an example of SQL Server INNER JOIN with the LIKE clause on multiple columns of tables in 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.COUNTRY_NAME LIKE 'U%'
AND COUNTRIES.COUNTRY_NAME LIKE '%a'
AND STATES.STATE_NAME LIKE 'a%';

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

    In the WHERE condition, we filtered the records of countries whose name starts with the letter U and ends with the letter a and have the state’s name with the letter ‘a‘ at the beginning for the resultset.

    Sql server inner join with like clause multiple columns example
    Example of SQL Server Inner Join with Like Clause Multiple Columns

    We hope that you have understood how to use the SQL Server INNER JOIN with the LIKE clause on multiple columns of tables in the query.

    Read: What is SQL Server Cross Join?

    SQL Server Inner Join With Like Clause Not Null

    Here we will see an example of SQL Server INNER JOIN with the LIKE clause and IS NOT NULL condition on tables by the following query:

    EXAMPLE:

    SELECT COUNTRIES.COUNTRY_NAME,
STATES.STATE_NAME,
CITY.CITY_NAME
FROM COUNTRIES
INNER JOIN STATES
ON COUNTRIES.COUNTRY_ID=STATES.COUNTRY_ID
INNER JOIN CITY
ON COUNTRIES.COUNTRY_ID=CITY.COUNTRY_ID
WHERE STATES.STATE_NAME LIKE '%k'
AND CITY.CITY_NAME IS NOT NULL;

    With the help of the INNER JOIN, we retrieved the records of the country’s name with the state’s name and city’s name from these tables i.e.; COUNTRIES, STATES and CITY by the WHERE condition.

    In the WHERE condition, we filter the state’s name whose string name ends with the letter k and doesn’t carry NOT NULL values in the resultset.

    Sql server inner join with like clause not null example
    Example of SQL Server Inner Join with Like Clause Not Null

    We hope that you have understood how to use the SQL Server INNER JOIN with the LIKE clause and IS NOT NULL condition on tables by the query.

    Read: SQL Server Outer Join With Count

    SQL Server Inner Join With Like Clause Row_Number

    Let’s see an example of SQL Server ROW_NUMBER function with INNER JOIN and LIKE clause on tables in the following query:

    EXAMPLE:

    SELECT ROW_NUMBER() OVER 
(ORDER BY STATES.STATE_NAME) STATES_PARTITION,
STATES.STATE_NAME,
COUNTRIES.COUNTRY_NAME
FROM STATES
INNER JOIN COUNTRIES
ON STATES.COUNTRY_ID=COUNTRIES.COUNTRY_ID
WHERE COUNTRIES.COUNTRY_NAME LIKE 'U%'
AND COUNTRIES.COUNTRY_NAME LIKE '%a';

    In the INNER JOIN query example, we extracted the row number based on ascending order of the state’s name and then retrieved records of the country’s name and state’s name from both tables i.e.; STATES and COUNTRY by the WHERE condition.

    In the WHERE condition, we filtered the country’s name whose string starts with the letter U and ends with the letter a for the resultset.

    Sql server inner join with like clause row_number example
    Example of SQL Server Inner Join with Like Clause Row_Number

    We hope that you have understood how to use SQL Server ROW_NUMBER function with the INNER JOIN and LIKE clause on tables by the query.

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

    We now know about the post “SQL Server INNER JOIN with LIKE clause “ 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 Like
      • Using SQL Server Inner Join with Like and Group By
      • How to use SQL Server Inner Join with Like and Having
      • SQL Server Inner Join with Like Multiple Columns
      • How to use SQL Server Inner Join With Like Not Null
      • Using SQL Server Inner Join With Like and Row_Number