How can i join two tables in mysql without joining?

Yes, you can! The longer answer is yes, there are a few ways to combine two tables without a common column, including CROSS JOIN (Cartesian product) and UNION. The latter is technically not a join but can be handy for merging tables in SQL. In this article, I’ll guide you through the different solutions with examples.

When Do you Need to Join Tables Without a Common Column?

Beginners just getting started with SQL are usually introduced to standard equi joins, in which two tables are combined by a common column. However, the real-world tasks of a data analyst are usually more diverse and complex.

For example, there are many use cases for a non-equi join, when two tables are combined using conditional operators other than the equal sign. If you are not familiar with this type of JOIN, check out our interactive SQL JOINs course. It offers over 90 hands-on exercises on different kinds of JOINs.

In other cases, you may want to have every combination of the rows from the two tables. As an example, if you run a restaurant, you might want to see all possible combinations of wines from your wine card and main courses from your menu.

Or, imagine that the information about your suppliers are stored in different tables. They may even have different column names by product type (i.e., wines, juices, fruits, etc.). You might want to combine all the information to generate a single table with all suppliers.

In these cases, you would want to combine two or more tables without requiring a common column. It sounds like it should be a pretty straightforward SQL assignment. Well, of course, it is! I’ll suggest a couple of solutions in this guide.

But first, let’s explore the data we’ll use for our examples.

Getting to Know the Data

We run a restaurant and have relevant data stored in multiple tables, among which are wine and main_course.

The wine table contains the wine ID, the wine name, the supplier ID, and the price:

The main_course table contains the main course ID, the name, the supplier ID of the major supplier for this dish, and the price, for each dish:

main_course

Let’s see how we can combine these tables to get the results we want.

How to Combine two Tables Without a Common Column

In our first example, we want to see all possible combinations of wines and main courses from our menu. There are at least two approaches to combining the wine and the main_course tables to get the result we need.

Using the “FROM Table1, Table2” Syntax

One way to join two tables without a common column is to use an obsolete syntax for joining tables. With this syntax, we simply list the tables that we want to join in the FROM clause then use a WHERE clause to add joining conditions if necessary.

If what we want is every combination of rows from two tables, there is no need to include any joining conditions. We can use a query like this:

SELECT w.name AS wine, m.name AS main_course
FROM wine w, main_course m;

The query will return a Cartesian product (a cross join), whose result set has the total number of rows equal to the number of rows in the first table multiplied by the number of rows in the second table.

We got the result we wanted. This is a workable solution for joining two tables when the objective is to get a result set with all possible combinations of the rows. However, this is not the best approach.

SQL practitioners prefer to use the explicit CROSS JOIN keyword for this kind of operation. It makes it clear that we are performing a cross join intentionally and not from accidentally omitting the join condition WHERE, which could happen when using the obsolete syntax for joining tables.

Read more about the benefits of the new syntax for joining tables in our article What's the Difference Between JOIN and Multiple Tables in FROM?

Using the CROSS JOIN Operator

As you may have already guessed, the second approach to getting all possible combinations of the rows from two tables is by using the CROSS JOIN operator:

SELECT w.name AS wine, m.name AS main_course
FROM wine w
CROSS JOIN main_course m;

This query outputs the exact same result set as shown earlier. However, this syntax is preferable as it clearly states the intent to perform a CROSS JOIN and is more readable for other users of your SQL code.

Learn more about cross joins in our Illustrated guide to SQL CROSS JOIN.

Using UNION or UNION ALL

There are other use cases for combining two tables without a common column. As in the example mentioned earlier, you may want to consolidate all the supplier information stored in multiple tables. You don’t want a Cartesian product in this case. So, how do you combine the tables?

In this case, you use a UNION to merge information from multiple tables. This is technically not a join; however, it can be very handy for combining rows from several tables, like in our example below.

Simply put, JOINs combine data by appending the columns from one table alongside the columns from another table. In contrast, UNIONs combine data by appending the rows alongside the rows from another table.

So, if we want to generate a combined list of the supplier IDs from the tables wine and main_course, we can use the following SQL query:

SELECT w.supplier_id
FROM wine w
UNION
SELECT m.major_supplier_id
FROM main_course m
ORDER BY supplier_id;

Here, we first select the supplier IDs from the wine table (column supplier_id) and from the main_course table (column major_supplier_id) separately. Then, we combine these rows using the UNION keyword. Finally, we sort the result for convenience:

Note the following when using UNION in SQL:

• All SELECT statements should list the same number of columns.
• The corresponding columns must have the same data type.
• The corresponding columns can have different names, as they do in our example. By default, the name of the corresponding column in the output will be taken from the first SELECT statement. If necessary, you can set the names for the resulting columns using the AS keyword. Note that the columns from different tables have to be named in the same order for them to correspond correctly.

• The UNION operator removes duplicates from the result set. As you may have noticed, the supplier_id 400 was in both tables, but it appears only once in the result set. If you do not want the duplicates removed, use the UNION ALL operator instead:

  SELECT w.supplier_id
  FROM wine w
  UNION ALL
  SELECT m.major_supplier_id
  FROM main_course m
  ORDER BY 1;
  

The result of this query will include the supplier_id 400 twice:

If neither CROSS JOIN nor UNION are applicable to your use case of combining two tables without a common column, check out this article with examples of non-equi joins. You may find some relevant examples there.

Time to Practice CROSS JOINs and UNIONs!

You now know a lot about combining tables without a common column. You’ll be prepared for the question on how to combine two tables without a common column. This is a very common question in SQL job interviews!

If you want to learn more about JOINs, check out the SQL JOIN Basics video from our “We Learn SQL” series on YouTube. Also, try LearnSQL.com’s guide on the best ways to learn SQL JOINs.

However, you probably already know that the shortest path to becoming an SQL expert is through lots of practice writing SQL queries. LearnSQL.com offers a comprehensive course on SQL JOINs with 93 interactive exercises. In this course, you’ll have the opportunity to practice all kinds of basic JOINs, including CROSS JOINs, self-joins, non-equi joins, and joins of multiple tables.

Happy learning!

How can I get data from two tables without joining?

Can we write join without on?

How can I join two tables in MySQL?

Can two tables be join without any relation?

How do you join two tables without common key fields between them?