The “JOIN” clause is used to combine the tables in MySQL, but the table can also be joined without using it. More specifically, the “UNION”, “UNION ALL” and the comma “,” are three different ways that can produce the same result as the “JOIN” clause. The comma can be used in place of the “JOIN” clause, Whereas, the “UNION” or “UNION ALL” are utilized with a “SELECT” statement to join the records of two tables.
This post will explain how to join two tables in MySQL:
Method 1: Join Two Tables in MySQL Using “,”
In MySQL, tables can be joined using “,” instead of the “JOIN” clause. Its syntax is given below:
FROM [table1-name], [table2-name];
The major difference is only replacing the keyword “JOIN” with “,”:
FROM [table1-name] JOIN [table2-name];
Use the comma “,” syntax with the “WHERE” clause to join the tables based on a particular condition. The syntax is given below:
FROM [table1-name], [table2-name]
WHERE [condition];
Example 1: Using “,” Without Condition
Let’s join “products” and “userdata” tables by specifying “,” as follows:
FROM products, userdata;
Output
The output depicts that the “products” and “userdata” tables have been joined without using the “JOIN” clause.
Example 2: Using “,” With Condition
Let’s see an example of joining two tables where “userdata.id” is greater than 2:
FROM products, userdata
WHERE userdata.id > 2;
Output
The output showed that the selected tables had been joined based on the given condition.
Method 2: Join Two Tables in MySQL Using “UNION” or “UNION ALL”
In MySQL, the “UNION” or “UNION ALL” operators can be used to merge the result sets of multiple SELECT statements. The syntax is given below:
UNION | UNION ALL
SELECT * FROM [table2-name]
The above syntax can be used with either “UNION” or “UNION ALL”.
Example 1: Using the “UNION” Operator
In MySQL, the “UNION” operator merges the results of multiple SELECT statements but eliminates any duplicate rows from the result. The query is given below:
UNION
SELECT * FROM employeedata;
Output
The output shows that the “userdata” and “employeedata” tables have been joined and duplicate rows have been eliminated.
Example 2: Using the “UNION ALL” Operator
In MySQL, the “UNION ALL” operator merges the results of multiple SELECT statements without eliminating the duplicate rows.
Let’s execute the following query for a profound understanding:
UNION ALL
SELECT * FROM employeedata;
Output
It can be observed that the resultant table contains the duplicated rows as well.
Example 3: Using “UNION” Operator With the Condition
In MySQL, the “UNION” operator can also be used with the “WHERE” clause to filter the result of two or more SELECT statements on the basis of a particular condition.
The example is given below:
UNION
SELECT id, ContactName, City, Country FROM Supplier WHERE Supplier.id <= 5;
Output
The output shows that the tables “employee” and “Supplier” have been joined under the specified conditions.
Conclusion
To join two or more tables, the “UNION“, “UNION ALL” and the comma “,” can be used instead of the “JOIN” clause. The comma is used by replacing the “JOIN” keyword. The “UNION” and “UNION ALL” can also be used to join multiple tables with or without duplicates. In this write-up, a detailed guide is presented on joining the tables without using the JOIN clause.
