SQL Server Select a Join Without Join

We will learn and comprehend how to use SQL Server SELECT statements in this lesson without using the JOIN clause on the query’s table(s).

Currently, I was working on the Employee Leave Management System in SQL Server. And I got a requirement that how I can use the SELECT statement without the JOIN clause and retrieve records from two tables by the query.

Here are the subtopics that we are going to discuss:

• How to select join with join in SQL Server
• How to select join with join in SQL Server using UNION
• How to select join with join in SQL Server using UNION ALL

Here is the ER diagram of the Employee Leave Management System given below:

SQL Server Select a Join Without Join

In SQL Server, it is possible to combine two tables without using the JOIN clause. But using the JOIN keyword is not the only way to join tables in SQL Server.

So there are two additional methods for joining tables. And we don’t need to use the JOIN clause to work. Here they are:

• So we will use the COMMA separator between two tables and specify the joining condition by using the WHERE clause.
• Use the UNION /UNION ALL keyword.

Let’s see the image of the MASTER TABLE i.e.; EMPLOYEES, EMPLOYEE_CREDENTIALS, and TIMESHEET tables which are given below:

The syntax of joining two tables without using the JOIN clause in SQL Server is given below:

SELECT * 
FROM TABLE_1, TABLE_2
WHERE TABLE_1.COMMON_COLUMN= TABLE_2.COMMON_COLUMN;

EXAMPLE:

SELECT * 
FROM EMPLOYEES,EMPLOYEE_CREDENTIALS
WHERE EMPLOYEES.EMP_ID=EMPLOYEE_CREDENTIALS.EMP_ID;

So without using the JOIN clause, we have used the SELECT statement to retrieve all records from both tables i.e; EMPLOYEES and EMPLOYEE_CREDENTIALS. And in the WHERE condition, the common column as EMP_ID column has to retrieve records based on the result set.

Additionally, the JOIN clause was dropped, and the WHERE condition rather than the ON condition from the previous query was used to match the records between the two tables.

Now let’s see the same result set by using the JOIN clause with a SELECT statement in the below query:

SELECT * 
FROM EMPLOYEES
INNER JOIN EMPLOYEE_CREDENTIALS
ON EMPLOYEES.EMP_ID=EMPLOYEE_CREDENTIALS.EMP_ID;

With the help of the INNER JOIN clause, the SELECT statement retrieves all records from the EMPLOYEES table and matching records from the EMPLOYEE_CREDENTIALS table.

As a piece of additional information, the method of the INNER JOIN clause and the FROM and WHERE clause resultset is the same. The only difference is the way of writing the query for the resultset.

In place of using the JOIN clause on two tables in a query, we hope you now understand how to use the SQL Server FROM and WHERE clauses with a SELECT statement.

Read: MySQL vs SQL Server

SQL Server Select a Join Without Join using Union

The UNION and UNION ALL operators in SQL Server are used to combine the results of the first query and the second query. The SQL Server UNION operator will remove all duplicate rows from the query and will show them once.

The EMPLOYEES and MANAGERS tables, which will be used with the UNION and UNION ALL operators, are listed below:

EXAMPLE:

SELECT FIRST_NAME 
FROM EMPLOYEES
UNION 
SELECT MANAGER_NAME 
FROM MANAGERS;

With the help of the UNION operator, the SELECT statement will remove all duplicate rows and will also FIRST_NAME and MANAGER_NAME columns in the result set.

Due to duplicate names like Chris and Ronaldo that have been deleted from both name columns of the tables, the result set will only contain a total of 14 records.

We hope that you have understood how to use SQL Server SELECT statement with the UNION operator and not use the JOIN clause on tables by the query.

Read: INSERT INTO SELECT Statement in SQL Server

SQL Server Select a Join Without Join using Union All

Here is an example of an SQL Server SELECT statement with the UNION ALL clause on tables and not using the JOIN clause on tables by the following query:

EXAMPLE:

SELECT FIRST_NAME 
FROM EMPLOYEES
UNION ALL 
SELECT MANAGER_NAME 
FROM MANAGERS;

In the SELECT statement, the UNION ALL clause returns all records as well as duplicate values of the FIRST_NAME and MANAGER_NAME columns. These values are from the EMPLOYEES and MANAGERS tables.

We hope that you have understood how to use SQL Server UNION ALL clause with the WHERE clause on two tables in a query.

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

• SQL Server logical operators and example
• How to use SQL Server Left Join on Distinct
• SQL Server Trigger Update with Examples
• SQL Server Trigger After Insert Update
• SQL Server Trigger Before Update

We now know about the topic “SQL Server Select a Join Without Join “ after reading this tutorial. To assist you in comprehending the idea, we explored the following subtopics in detail.

• How to select join with join in SQL Server
• How to select join with join in SQL Server using UNION
• How to select join with join in SQL Server using UNION ALL