Can you join two unrelated tables in SQL? Cross Join Example

In one of the recent programming job interviews, one of my readers was asked the question, how do you join two tables which are not related to each other? i.e. they don't have any common column? is it possible in SQL? My reader got confused because he only knows about INNER join and OUTER join which require a key column like dept_id which is the primary key in one table like Department and foreign key in another table like Employee. He couldn't answer the question, though he did tell them about you can select data from multiple tables by typing multiple table names in from clause using a comma. 

What he was saying was right, but he didn't know much about it, so he couldn't explain it confidently. Lucky for him, he got the job but he emailed me asking the same question which inspired me to write this post.

The answer to this question is yes, you can join two unrelated tables in SQL, and in fact, there are multiple ways to do this, particularly in the Microsoft SQL Server database. The most common way to join two unrelated tables is by using CROSS join, which produces a cartesian product of two tables.

For example, if one table has 100 rows and another table has 200 rows then the result of the cross join will contain 100x200 or 20000 rows.

The obvious way of doing a cross join in SQL Server is by using the keyword CROSS JOIN itself as shown below:

SELECT t1.column_1, t1.column_2, t2.column_1 FROM TABLE t1 CROSS JOIN TABLE t2

This will produce a result set containing column_1 and column_2 from Table1 and column_3 from Table2 and it will contain r1xr2 records where r1 is the total number of records in Table1 and r2 is the total number of records in table 2. You can find more information about CROSS Join these free SQL and Database courses on Udemy, one of my favorite courses to learn about Microsoft SQL Server and other databases for free. 

Cross Join Example of joining two unrelated tables in SQL

Let's see a more concrete example of joining two unrelated tables. In this example, I have two tables, FirstName and LastName and when I join them it will print FirstName, LastName by doing a cartesian join as shown below:

CREATE TABLE FirstName (first_name VARCHAR(14)) ;
 
INSERT INTO FirstName
([first_name])
VALUES
('John'),
('Marry'),
('Elizabeth')
;
 
CREATE TABLE LastName ( last_name VARCHAR(10));
 
INSERT INTO LastName
([last_name])
VALUES
('Taylor'),
('Dsouza'),
('Paul'),
('Root')
;

Now, let's join these two tables, remember they are unrelated there is no common column between these two tables.

SELECT * FROM FirstName CROSS JOIN LastName

(12 row(s) affected)

You can see that the result of the SELECT query contains 12 rows, which is 4 x 3 because the first table contains 4 rows and the second table contains 3 rows. If you don't understand CROSS JOIN, I suggest you take a look at a comprehensive SQL course like The Complete SQL Bootcamp by Jose Pottlia on Udemy.

Here is the output:

Btw, this is not the only way to do the cross join. As stated in the first paragraph if you simply list the two tables in from clause it will do a cross join as shown below:

SELECT * FROM FirstName, LastName ORDER BY last_name

This is actually quite dangerous because if you get into a habit then you will do the INNER join like this by adding a WHERE clause and if such operation is performed on a large table containing 1 million rows, then it will take a long time and can potentially lock the tables, preventing other jobs and users from accessing data.

One more way of joining two unrelated tables in SQL by using the "CROSS APPLY" command of SQL Server, it behaves similar to CROSS JOIN as shown below:

SELECT * FROM FirstName CROSS apply LastName ORDER BY first_name

The CROSS APPLY feature depends upon APPLY operator which is available only from SQL Server 2005. Even though CROSS JOIN and CROSS APPLY look similar, they are not different. The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. 

You can also see SQL Server Fundamentals by Dan Sullivan course on Pluralsight to learn more about APPLY operators. It's one of the best courses to learn Microsoft SQL Server specific concepts and features online.

The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input."


That's all about whether you can join two unrelated tables in SQL or not. Yes, you can join them using CROSS join which returns the cartesian product of tables in the query, so if you have 10 rows in each table then the query will return 100 rows. So be careful while using CROSS JOIN on large tables containing millions of rows.


Other SQL Server Interview Questions you may like

• Difference between char and varchar in SQL? (answer)
• Top 5 SQL and Database Course for Programmers (courses)
• Difference between a table scan and an index scan in SQL Server? (answer)
• What is the difference between UNION and UNION ALL in SQL? (answer)
• The difference between TRUNCATE and DELETE in SQL? (answer)
• The difference between self and equi-join in SQL? (answer)
• The difference between WHERE and HAVING clause in SQL? (answer)
• How to delete from a table using join in SQL? (tutorial)
• How to find duplicate records in a table? (query)
• Difference between isNull() and Coalesce() in SQL Server? (answer)
• 10 SQL Queries from Programming Job Interviews (queries)
• 5 Courses to Learn SQL Server and T-SQL (Courses)

Thanks for reading this article so far. If you like this SQL Interview question and my explanation then please share it with your friends and colleagues. If you have any questions or feedback then please drop a note.

P. S. - If you need more SQL Interview Questions for practice, you can also check out free SQL and Database courses course on Udemy which not only tells you the common SQL questions but also explain the answer in detail to fill gaps in your learning.