Can You Lot Bring Together Ii 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 gain yous bring together 2 tables which are non related to each other? i.e. they don't receive got whatever mutual column? is it possible inwards SQL? My reader got confused because he exclusively knows almost INNER join together with OUTER join which require a primal column similar dept_id which is the main primal inwards i tabular array similar Department together with foreign key inwards around other tabular array similar Employee. He couldn't respond the question, though he did tell them almost yous tin conduct information from multiple tables yesteryear typing multiple tabular array names inwards from clause using a comma. What he was proverb was right, but he didn't know much almost it, thence he couldn't explicate it confidently. Lucky for him, he got the project but he emailed me yell for the same query which inspired me to write this post.

The respond to this query is yes, yous tin bring together 2 unrelated tables inwards SQL together with inwards fact, at that spot are multiple ways to gain this, peculiarly inwards the Microsoft SQL Server database. The most mutual means 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 around other tabular array has 200 rows together with thence the final result of the cross bring together volition comprise 100x200 or 20000 rows.

The obvious means of doing a cross bring together inwards SQL Server is yesteryear using the keyword CROSS JOIN itself every bit shown below:

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

This volition gain a final result gear 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 sum disclose of records inwards Table1 together with r2 is the sum disclose of records inwards tabular array 2. You tin detect to a greater extent than information almost 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 instance of joining 2 unrelated tables inwards SQL

Let's come across a to a greater extent than concrete instance 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 every bit 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, recall they are unrelated there is no mutual column betwixt these 2 tables.

SELECT * FROM FirstName CROSS JOIN LastName

(12 row(s) affected)

You tin come across that the final result of the SELECT query contains 12 rows, which is iv x 3 because the kickoff tabular array contains iv rows together with the instant tabular array contains 3 rows. If yous don't sympathise CROSS JOIN, I advise yous receive got a hold off 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 means to gain the cross join. As stated inwards the kickoff paragraph if yous exactly listing the 2 tables inwards from clause it volition gain a cross bring together every bit shown below:

SELECT * FROM FirstName, LastName ORDER BY last_name

This is genuinely quite dangerous because if yous acquire into habit together with thence yous volition gain 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 meg rows, together with thence it volition receive got a long fourth dimension together with tin potentially lock the tables, preventing other jobs together with users from accessing data.

One to a greater extent than means of joining 2 unrelated tables inwards SQL yesteryear using the "CROSS APPLY" ascendence of SQL Server, it behaves similar to CROSS JOIN every bit 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 hold off similar, they are non different. The APPLY operator allows yous to invoke a table-valued purpose for each row returned yesteryear an outer tabular array facial expression of a query. You tin see answer)

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

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

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

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

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

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

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

How to detect duplicate records inwards a table? (query)

Difference betwixt isNull() together with Coalesce() inwards 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 thence far. If yous similar this SQL Interview query together with my explanation together with thence delight part amongst your friends together with colleagues. If yous receive got whatever questions or feedback together with thence delight driblet a note.

P.S. - If yous demand to a greater extent than SQL Interview Question for practice, yous tin too depository fiscal establishment jibe out 200+ SQL Interview Questions online course on Udemy which non exclusively tell yous the mutual SQL questions but too explicate the respond inwards special to create sum gaps inwards your learning.