Can You Lot Bring Together 2 Unrelated Tables Inwards Sql? Cross Bring Together Example

In i of the recent programming project interview, i of my readers was asked the question, how arrive at y'all bring together 2 tables which are non related to each other? i.e. they don't receive got whatsoever mutual column? is it possible inward SQL? My reader got confused because he exclusively knows virtually INNER join together with OUTER join which require a telephone substitution column similar dept_id which is the principal telephone substitution inward i tabular array similar Department together with foreign key inward about other tabular array similar Employee. He couldn't reply the question, though he did tell them virtually y'all tin sack lead information from multiple tables yesteryear typing multiple tabular array names inward from clause using a comma. What he was maxim was right, but he didn't know much virtually it, hence he couldn't explicate it confidently. Lucky for him, he got the project but he emailed me shout out for the same inquiry which inspired me to write this post.

The reply to this inquiry is yes, y'all tin sack bring together 2 unrelated tables inward SQL together with inward fact, in that place are multiple ways to arrive at this, especially inward the Microsoft SQL Server database. The most mutual agency to bring together 2 unrelated tables is yesteryear using CROSS join, which produces a cartesian production of 2 tables.

For example, if i tabular array has 100 rows together with about other tabular array has 200 rows hence the termination of the cross bring together volition comprise 100x200 or 20000 rows.

The obvious agency of doing a cross bring together inward SQL Server is yesteryear using the keyword CROSS JOIN itself equally shown below:

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

This volition create a termination laid upward containing column_1 together with column_2 from Table1 together with column_3 from Table2 together with it volition comprise r1xr2 records where r1 is the full publish of records inward Table1 together with r2 is the full publish of records inward tabular array 2. You tin sack abide by to a greater extent than information virtually CROSS Join on Microsoft SQL for Beginners course on Udemy, i of my favorite course of report to larn Microsoft SQL Server.

A Cross Join lawsuit of joining 2 unrelated tables inward SQL

Let's encounter a to a greater extent than concrete lawsuit of joining 2 unrelated tables. In this example, I receive got 2 tables, FirstName together with LastName together with when I bring together them it volition impress FirstName, LastName yesteryear doing a cartesian join equally 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 bring together these 2 tables, shout out upward they are unrelated there is no mutual column betwixt these 2 tables.

SELECT * FROM FirstName CROSS JOIN LastName

(12 row(s) affected)

You tin sack encounter that the termination of the SELECT query contains 12 rows, which is iv x 3 because the offset tabular array contains iv rows together with the bit tabular array contains 3 rows. If y'all don't empathize CROSS JOIN, I propose y'all receive got a await at a comprehensive SQL course of report like The Complete SQL Bootcamp by Jose Pottlia on Udemy.

Here is the output:

Btw, this is non the exclusively agency to arrive at the cross join. As stated inward the offset paragraph if y'all but listing the 2 tables inward from clause it volition arrive at a cross bring together equally shown below:

SELECT * FROM FirstName, LastName ORDER BY last_name

This is genuinely quite dangerous because if y'all acquire into habit hence y'all volition arrive at the INNER bring together similar this yesteryear adding a WHERE clause together with if such functioning is performed on a large tabular array similar containing 1 1000000 rows, hence it volition receive got a long fourth dimension together with tin sack potentially lock the tables, preventing other jobs together with users from accessing data.

One to a greater extent than agency of joining 2 unrelated tables inward SQL yesteryear using the "CROSS APPLY" ascendency of SQL Server, it behaves similar to CROSS JOIN equally shown below:

SELECT * FROM FirstName CROSS apply LastName ORDER BY first_name

The CROSS APPLY characteristic depends upon APPLY operator which is available exclusively from SQL Server 2005. Even though CROSS JOIN together with CROSS APPLY await similar, they are non different. The APPLY operator allows y'all to invoke a table-valued component subdivision for each row returned yesteryear an outer tabular array appear of a query. You tin sack see answer)

Top v SQL together with Database Course for Programmers (courses)

Difference betwixt a tabular array scan together with an index scan inward SQL Server? (answer)

What is the divergence betwixt UNION together with UNION ALL inward SQL? (answer)

The divergence betwixt TRUNCATE together with DELETE inward SQL? (answer)

The divergence betwixt self together with equi-join inward SQL? (answer)

The divergence betwixt WHERE together with HAVING clause inward SQL? (answer)

How to delete from a tabular array using bring together inward SQL? (tutorial)

How to abide by duplicate records inward a table? (query)

Difference betwixt isNull() together with Coalesce() inward SQL Server? (answer)

10 SQL Queries from Programming Job Interviews (queries)

5 Courses to Learn SQL Server together with T-SQL (Courses)

Thanks for reading this article hence far. If y'all similar this SQL Interview inquiry together with my explanation hence delight portion alongside your friends together with colleagues. If y'all receive got whatsoever questions or feedback hence delight driblet a note.

P.S. - If y'all demand to a greater extent than SQL Interview Question for practice, y'all tin sack likewise banking concern fit out 200+ SQL Interview Questions online course on Udemy which non exclusively tell y'all the mutual SQL questions but likewise explicate the reply inward item to fill upward gaps inward your learning.

Share this post