It's one of the best courses to learn Microsoft SQL Server specific concepts and features online. You can also see SQL Server Fundamentals by Dan Sullivan course on Pluralsight to learn more about APPLY operators. The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. Even though CROSS JOIN and CROSS APPLY look similar, they are not different. The CROSS APPLY feature depends upon APPLY operator which is available only from SQL Server 2005. SELECT * FROM FirstName CROSS apply LastName ORDER BY first_name 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: 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. SELECT * FROM FirstName, LastName ORDER BY last_name 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: 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.ītw, this is not the only way to do the cross join. 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. SELECT * FROM FirstName CROSS JOIN LastName Now, let's join these two tables, remember they are unrelated there is no common column between these two tables. ( ) VALUES ( 'John' ), ( 'Marry' ), ( 'Elizabeth' )ĬREATE TABLE LastName ( last_name VARCHAR ( 10 ) ) 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:ĬREATE TABLE FirstName (first_name VARCHAR ( 14 ) ) Let's see a more concrete example of joining two unrelated tables. 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.Ĭross Join Example of joining two unrelated tables in SQL 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. column_1 FROM TABLE t1 CROSS JOIN TABLE t2 The obvious way of doing a cross join in SQL Server is by using the keyword CROSS JOIN itself as shown below: The most common way to join two unrelated tables is by using CROSS join, which produces a cartesian product of two tables.įor 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 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. Lucky for him, he got the job but he emailed me asking the same question which inspired me to write this post. What he was saying was right, but he didn't know much about it, so he couldn't explain it confidently.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |