How to Use Select Statement Without Join

I am currently working on the Employee Leave Management System in SQL Server. I have a requirement to use the SELECT statement without the JOIN clause to retrieve records from two tables by query.

This tutorial will teach how to use SQL Server SELECT statements without using the JOIN clause on the query’s table.

SQL Server Select a Join Without Join

In SQL Server, combining two tables without using the JOIN clause is possible. However, 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 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, EMPLOYEES and EMPLOYEE_CREDENTIALS. In the WHERE condition, the common column, EMP_ID, has to retrieve records based on the result set.

The JOIN clause was also dropped, and the records between the two tables were matched using the WHERE condition rather than the ON condition from the previous query.

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

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.

Note – 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.

SQL Server Select a Join Without Join using Union

The UNION and UNION ALL operators in SQL Server combine the results of the first and second queries. The SQL Server UNION operator removes all duplicate rows from the query and shows them once.

Below are the EMPLOYEES and MANAGERS tables, which will be used with the UNION and UNION ALL operators.

EXAMPLE:

SELECT FIRST_NAME 
FROM EMPLOYEES
UNION 
SELECT MANAGER_NAME 
FROM MANAGERS;

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

Due to duplicate names like Chris and Ronaldo, which have been deleted from both name columns of the tables, the result set will only contain 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.

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.

Conclusion

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

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