There are few ways to combine the two tables without a common column including Cross Join (Cartesian Product) and UNION. This is not a join but can be useful for merging tables in SQL. In this post, I’ll explain different solutions with examples.
Here is the example Below.
Suppose there is a restaurant and they have two tables wine and main_courser in their database.
The wine table contains the wine ID, the name of the wine, the supplier ID, and the price:
| id | name | supplier_id | price |
| 1 | Merlot | 500 | 7.95 |
| 2 | House | 400 | 2.45 |
| 3 | Sangiovese | 600 | 5.20 |
The main course table contains the main course id, the main course name, the major supplier ID for this meal, and the price:
| id | name | major_supplier_id | price |
| 1 | Cardamom Maple Salmon | 200 | 19.99 |
| 2 | Classic Macaroni & Cheese | 100 | 8.99 |
| 3 | Baked Teriyaki Chicken | 300 | 11.99 |
| 4 | Blue Cheese Beef Tenderloin | 400 | 15.99 |
In our first example, we want all the possible combinations of wines and main course for our menu.
First, we will use the FROM TABLE 1, TABLE 2 Syntax:
We simply join the two tables together with this syntax. Two tables are joined with the help of FROM clause and then use the WHERE clause if necessary.
There is no need to specify any joining conditions if all we want is every combination of rows from two tables. A query like this can be used:
SELECT w.name AS wine, m.name AS main_course
FROM wine w, main_course m;
This query provides a cross join, or cartesian product, with a total number of rows equal to the first table’s number of rows multiplied by the second table’s number of rows.
| wine | main_course |
| Merlot | Cardamom Maple Salmon |
| House | Cardamom Maple Salmon |
| Sangiovese | Cardamom Maple Salmon |
| Merlot | Classic Macaroni & Cheese |
| House | Classic Macaroni & Cheese |
| Sangiovese | Classic Macaroni & Cheese |
| Merlot | Baked Teriyaki Chicken |
| House | Baked Teriyaki Chicken |
This is one of the approaches for joining two tables together when the objective is to get a result set with all possible combinations of the rows but this is not the best approach.
Mostly, CROSS JOIN is used for this type of operation.
Using the CROSS JOIN:
SELECT w.name AS wine, m.name AS main_course
FROM wine w
CROSS JOIN main_course m;
This query produces the same result as earlier. This syntax is more readable for the users and it also clearly states the purpose of CROSS JOIN.
Using UNION or UNION ALL:
There are additional reasons to join two tables that don’t have a common column. You may want to integrate all of the supplier information maintained in several tables, as in the previous example. In this scenario, you don’t want a Cartesian product. So, how do you put the tables together?
In this scenario, a UNION is used to combine data from different tables. Although this is not exactly a join, it can be highly useful for integrating rows from multiple tables, as in the example below.
Simply put, JOINS joins two tables together by adding the columns from one table to the columns from the other. UNIONs, on the other hand, merge data by appending rows from one table to the rows from another.
So, if we want to get a list of all the supplier IDs from the databases wine and main course, we may use the SQL query below:
SELECT w.supplier_id
FROM wine w
UNION
SELECT m.major_supplier_id
FROM main_course m
ORDER BY supplier_id;
We select the supplier IDs individually from the wine table (column supplier id) and the main course table (column-major supplier id). The UNION keyword is then used to join these rows together. Finally, for ease of use, we order the results:
| supplier_id |
| 100 |
| 200 |
| 300 |
| 400 |
| 500 |
| 600 |
Note the following when using UNION in SQL:
The number of columns in each SELECT statement should be the same.
The data types of the related columns must be the same.
As seen in our example, the related columns can have different names. By default, the first SELECT statement is used to determine the name of the relevant column in the output. The AS keyword can also be used to change the names of the generated columns.
The UNION operator removes duplicate records. Although the supplier id 400 is present in both tables, it only appears once in the result set. If you don’t want duplicate records, use UNION ALL:
SELECT w.supplier_id
FROM wine w
UNION ALL
SELECT m.major_supplier_id
FROM main_course m
ORDER BY 1;
The supplier_id 400 is twice in this query:
| supplier_id |
| 100 |
| 200 |
| 300 |
| 400 |
| 400 |
| 500 |
| 600 |
Study Section provides a big list of certification exams through its online platform. The French Certification Exam can help you to certify your skills to communicate in the French language. Whether you are new to the language or you are an expert in it, this French certification exam can test the ability of anybody’s command over the French language.
