SQL Server EQUI JOIN + Examples

by
Sql server equi join example

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

The SQL Server EQUI JOIN is quite different from other types of SQL Server JOINS. In EQUI JOIN, we have used the WHERE condition for joining both tables. Thus EQUI JOINS in SQL joins multiple tables on the basis of an equality condition.

In contrast, the NON-EQUI JOINS joins the table on the basis of conditions other than the equality conditions, such as !=, >, <, etc.

Let’s understand the definition by using the different subtopics:

  • Define SQL Server Equi Join
  • Define SQL Server Non-Equi Join
  • What is the difference between SQL Server Equi Join and Inner Join
  • How to use SQL Server Equi Join Count
  • How do we use SQL Server Equi Join Group By
  • How to use SQL Server Equi Join Having
  • Use of SQL Server Equi Join Like
  • How to use SQL Server Equi Join Order By

SQL Server Equi Join

In SQL Server, the EQUI JOIN clause is an operation that combines multiple columns based on equality and or matching column values in the associated tables. Points need to be remembered while using the SQL Server EQUI JOIN:

  • There is no need to have the same column names.
  • The resultant result can have repeated column names.
  • We can use the EQUI JOIN operation on more than two tables.

Let’s see syntax and also an example of SQL Server EQUI JOIN on multiple tables by the query.

SYNTAX:

SELECT COLUMN_NAME(S)
FROM TABLE_1, TABLE_2,TABLE_N
WHERE TABLE_1.COMMON_COLUMN=TABLE_2.COMMON_COLUMN;

EXAMPLE:

SELECT COUNTRIES.COUNTRY_NAME,
STATES.STATE_NAME
FROM COUNTRIES,STATES
WHERE COUNTRIES.COUNTRY_ID=STATES.COUNTRY_ID;

With the help of the EQUI JOIN query example, we retrieve all records of the country’s name with their state’s name from both tables i.e., COUNTRIES and STATES by the WHERE condition.

In the WHERE condition, we used the EQUAL TO operator to retrieve all records from both tables by using the COUNTRY_ID column in the resultset.

Sql server equi join example
Example of SQL Server EQUI JOIN clause

We hope you understand the definition of the SQL Server EQUI JOIN and explained its syntax by an example.

Read: SQL Server SELF JOIN 

SQL Server Non-Equi Join

In SQL Server, the NON-EQUI JOIN uses a comparison operator other than the EQUAL TO operator sign like ( >, <,>=,<=). Let’s see the syntax of SQL Server NON-EQUI JOIN and also an example by the query.

SYNTAX:

SELECT * 
FROM TABLE_1,TABLE_2
WHERE TABLE_1.COMMON_COLUMN [ < | > | <= | >= ] TABLE_2.COMMON_COLUMN;

EXAMPLE:

SELECT STATES.STATE_NAME,
CITY.CITY_NAME
FROM STATES,CITY
WHERE STATES.STATE_ID >= CITY.STATE_ID;

With the help of the NON-EQUI JOIN in the SELECT statement, we retrieve records of the state’s name with their city’s name from both tables: STATES and CITY by the WHERE condition.

In the WHERE condition, we filter the common column as STATE_ID with the GREATER THAN operator in the STATES table rather than the CITY table. This will bring all records of the state’s name multiple times with the same name as the state’s name in the resultset.

Sql server non equi join example
Example of SQL Server NON-EQUI JOIN clause

We hope you understand the definition of SQL Server NON-EQUI JOIN and explained its syntax with an example on the tables by the query.

Read: SQL Server OUTER JOIN

SQL Server Equi Join Vs Inner Join

Here, we will understand the difference between the SQL Server EQUI JOIN and INNER JOIN on tables by the query.

SQL Server EQUI JOIN is a join with a join condition containing an equality operator. And it returns only rows that have equivalent values for the specified columns.

An SQL Server INNER JOIN is a join of two or more tables that returns only those rows (compared using a comparison operator) that satisfy the join condition. And there is no concept of using the SQL Server LEFT INNER JOIN or RIGHT INNER JOIN.

Here are the SQL Server EQUI JOIN forms:

  • The joins are formed based on the equal ‘=’ condition in the ‘WHERE’ clause. I meant to say that in the ‘WHERE‘ clause you can give a ‘>’ or a ‘<‘ condition. In the EQUI JOIN scenario, only an equal condition is specified.
  • Do not assume that we can perform a join on two or more tables.
  • In SQL Server EQUI JOIN the join condition is usually given in the ‘WHERE’ clause. And we have already explained it under the SQL Server EQUI JOIN subtopic.

Now, let’s understand how the SQL Server INNER JOIN forms:

  • In the SQL Server INNER JOIN, only matching rows of the first_table (TABLE_A), will be formed in the result.
  • In the SQL Server query, first, it looks for matching rows from matching TABLE_A and TABLE_B. But if rows are present, then it will show in the resultset.
  • We can give the INNER JOIN any condition like ‘ = ‘, ‘ < ‘, ‘ > ‘. And it is different from the SQL Server EQUI JOIN clause.

Let’s see an example of SQL Server INNER JOIN clause on tables by the query.

EXAMPLE:

SELECT COUNTRIES.COUNTRY_NAME,
STATES.STATE_NAME
FROM COUNTRIES
INNER JOIN STATES
ON COUNTRIES.COUNTRY_ID=STATES.COUNTRY_ID;

With the help of the INNER JOIN query example, we retrieve all records of the country’s name with their state’s name from both tables: COUNTRIES and STATES.

Sql server equi join vs inner join example
Example of SQL Server INNER JOIN clause

We hope you understand the difference between the SQL Server EQUI JOIN and INNER JOIN on tables by the example.

Read: SQL Server LEFT JOIN Tutorial

SQL Server Equi Join Count

Let’s see an example of SQL Server COUNT function with the EQUI JOIN on tables by the query.

EXAMPLE:

SELECT COUNT(CITY.CITY_NAME) AS CITY_PERSTATES,
STATES.STATE_NAME
FROM STATES,CITY
WHERE STATES.STATE_ID=CITY.STATE_ID
GROUP BY STATES.STATE_NAME;

With the help of the COUNT function in the SELECT statement, we counted the city’s name based on the state’s name from both tables i.e., STATES and CITY.

Sql server equi join count example
Example of SQL Server COUNT function with EQUI JOIN clause

We hope you understand an example of SQL Server COUNT function with the EQUI JOIN on tables by the query.

Read: RIGHT JOIN in SQL Server

SQL Server Equi Join Having

In this SQL Server subtopic section, we will see an example of SQL Server EQUI JOIN with the HAVING clause on tables by the query.

EXAMPLE:

SELECT COUNT(STATES.STATE_NAME) AS COUNTRY_PERSTATES,
COUNTRIES.COUNTRY_NAME
FROM COUNTRIES,STATES
WHERE COUNTRIES.COUNTRY_ID=STATES.COUNTRY_ID
GROUP BY COUNTRIES.COUNTRY_NAME
HAVING  COUNT(STATES.STATE_NAME) >6;

With the help of the EQUI JOIN query example, we counted the state’s name based on the country’s name from both tables i.e., COUNTRIES and STATES by the HAVING clause.

In the HAVING clause, we counted the state’s name whose value is greater than 6. This will bring records of the country’s name in the resultset.

Sql server equi join having example
Example of SQL Server EQUI JOIN with HAVING clause

We hope you understand how to use the SQL Server EQUI JOIN with the HAVING clause on tables by the query.

Read: SQL Server Left Join With Count

SQL Server Equi Join Like

In this SQL Server subtopic section, we will see an example of SQL Server EQUI JOIN with the LIKE clause on tables by the query.

EXAMPLE: 

SELECT COUNTRIES.COUNTRY_NAME,
STATES.STATE_NAME
FROM COUNTRIES,STATES
WHERE COUNTRIES.COUNTRY_NAME LIKE 'U%';

With the help of the LEFT JOIN in the SELECT statement, we retrieve all records of the country’s name with their state’s name from both tables i.e., COUNTRIES, STATES by the WHERE condition.

In the WHERE condition, we filter the record of the country’s name whose name starts with the letter ‘U’. This will bring all records of the state’s name and multiple country’s names with the letter ‘U’ in the resultset.

Sql server equi join like example
Example of SQL Server EQUI JOIN with LIKE clauses

We hope you understand an example of SQL Server EQUI JOIN with the LIKE clause on tables by the query.

Read: SQL Server Outer Join With Count

SQL Server Equi Join Order By

Here is an example of SQL Server EQUI JOIN with the ORDER BY clause.

EXAMPLE:

SELECT COUNTRIES.COUNTRY_NAME,
STATES.STATE_NAME
FROM COUNTRIES,STATES
ORDER BY COUNTRIES.COUNTRY_ID DESC;

Our SELECT statement uses the EQUI JOIN clause to retrieve all records of the country’s name and state’s name from both tables: COUNTRIES and STATES.

In the ORDER BY clause, we arranged the records of country ID in descending order. So, it can bring the records of multiple times of the same country’s name with their state’s name in the resultset.

Sql server equi join order by example
Example of SQL Server EQUI JOIN with ORDER BY clauses

Hopefully, you now understand how to use the SQL Server EQUI JOIN on tables in the query, in conjunction with the LIKE clause, to perform a join.

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

The following is a list of every subtopic discussed in this tutorial to assist you in better understanding the topic “SQL Server EQUI JOIN”.

  • Define SQL Server Equi Join
  • Define SQL Server Non-Equi Join
  • What is the difference between SQL Server Equi Join and Inner Join?
  • How to use SQL Server Equi Join Count
  • How do we use SQL Server Equi Join Group By
  • How to use SQL Server Equi Join Having
  • Use of SQL Server Equi Join Like
  • How to use SQL Server Equi Join Order By