Remember that instead of a table you can use anything that produces rows, like a sub-query. UNION and JOIN within a FROM clause are supported within views and in derived tables and subqueries. VERY GOOD ARTICLE.TOTALLY UNDERSTANDABLE. Suppose, the A table has N rows and B table has M rows, the CROSS JOIN of these two tables will produce a result set that contains NxM rows.. I have a problem that I'm trying to solve, but after very long hours of trying, I couldn't found the solution. leftouterjoinAccountt2on (t1.AccountName=t2.Name), crossapply(selectstuff((select', '+t.nameas[text()], select'GlobalCRMId'asnamewheret1.globalcrmidisnullort2.Accountnumberisnull, unionallselect'AccountName'wherenot((t1.AccountNameisnullandt2.Nameisnull)or(t1.AccountName=t2.Name)), unionallselect'AccountID'wherenott1.globalcrmid=t2.Accountnumber, unionallselect'AccountName'wherenott1.AccountName=t2.ParentAccountIdName, unionallselect'StreetAddress1'wherenott1.StreetAddress1=t2.Address1_Line1, unionallselect'StreetAddress2'wherenott1.StreetAddress2=t2.Address1_Line2, unionallselect'StreetAddress3'wherenott1.StreetAddress3=t2.Address1_Line3, ).value('. joining Other than with a cross join however, the … For those rows Applying a Sub-Query, Joining a Derived Table … I think the easiest way to think of CROSS APPLY is that it is like doing a CROSS JOIN with a correlated sub-query instead of a derived table. In Math, a Cartesian product is a mathematical operation that returns a product set of multiple sets. Now I understand how to use APPLY operator. The only real difference that CROSS APPLY and OUTER APPLY is better than LEFT and INNER JOIN is when using a function or you want to write an advanced SQL Command to bring back certain results Run the following script in a SQL Server Environment to see these operators in action. to delve into much details rather here are some articles that discuss this topic the right table expression returns rows for the left table expression match only. We will pass 3 as the author id to the fnGetBooksByAuthorId function. It retrieves all the records from the table where there are corresponding matching rows in the output returned by the table valued function. I'm confused. expression. equal query cost, as you can see in the image below. It retrieves those records from the table valued function and the table being joined, where it finds matching rows between the two. To retrieve all the records from Author table, LEFT JOIN can be used. The following example uses a pipelined table functionon the right side of the join. why cross apply need in sql server . It retrieves those records from the table valued function and the table being joined, where it finds matching rows between the two. First, let’s create a dummy database with some dummy records in it. CROSS APPLY CROSS APPLY is similar to INNER JOIN, but can also be used to join table-evaluated functions with SQL Tables. From Stack Overflow:. I will discuss this later. We first used JOIN operators to join two physical tables. I have one scalar function.. being executed by the current session. The output of the above query looks like this: You can see that all the records are retrieved from the Author table, irrespective of there being any matching rows in the Book table. Some names and products listed are the registered trademarks of their respective owners. I appriciate this. with a … The CROSS APPLY operator returns only those rows from left table expression (in its final output) if it matches with right table expression" is not the whole story. Use a CROSS JOIN instead of an INNER JOIN and the next developer who looks at … Our brain will receive this signal and begin to generate all meal and drink combinations. query uses the OUTER APPLY in place of the CROSS APPLY and hence unlike the CROSS APPLY Book has an author_id column which contains values from the id column of the Author table. For those rows for which there is not a match in the Employee table, those SQL-92 syntax provides the INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, and CROSS join operators. Thanks a lot for posting this, it helped me a great deal with understanding what APPLY does, and how it's different from a JOIN. The example below uses a correlated inline view. Great post! the active part of the query. query you can use the statement_start_offset and statement_end_offset columns to trim an APPLY operator performs better than a query with regular joins. But the following seems to do just that in a simpler and more readable format (because functionality is not black-boxed in a function): select * from department d inner join employee e on d.departmentid=e.departmentid. irrespective of its match with the right table expression. Simple and clear. like a Thanks for the overview, but I'm still unsure about the need for cross apply. A very interesting type of JOIN is the LATERAL JOIN (new in PostgreSQL 9.3+), which is also known as CROSS APPLY/OUTER APPLY in SQL-Server & Oracle. This is very clear and understandable article. The second query simply joins the Department table with the Employee table and Thu Oct 18, 2007 by Jeff Smith in t-sql, techniques, sql-server-2005, joins-relations. Although the same can be achieved for SQL Server 2005. Please Simple CROSS JOIN Example. Compare SQL Server Datasets with INTERSECT and EXCEPT, Join SQL Server tables where columns include NULL values. with an INNER JOIN/LEFT OUTER JOIN, specifying the ON clause with 1=1 and run the It returns all rows from the left hand table, where at least one row is returned by the table reference or collection expression on the right. the query with the OUTER APPLY uses a Compute Scalar operator (with estimated have small doubt in sql server . As ever if you are trying things out on a live database be sure to check that you are fully backed up. The output of the above function looks like this: You can see that all the records from the Author table have been retrieved irrespective of the matching rows in the output from the table valued function fnGetBookByAuthorId. We will see how they are implemented practically with the help of an example and will also discuss how they differ from each other. How does it differ from a JOIN and how When splitting using this line: *from dstools.dbo.zSplitlines('UN1016, CARBON MONOXIDE, COMPRESSED, 2.3 (2.1) POISON GAS, FLAMMABLE GAS, INHALATION HAZARD, ZONE D',32,' '), LineNumber Line1 UN1016, CARBON MONOXIDE,2 COMPRESSED, 2.3 (2.1) POISON3 GAS, FLAMMABLE GAS, INHALATION4 HAZARD, ZONE D. However, if I use a delim ',' I get this: LineNumber Line1 UN1016, CARBON MONOXIDE,2 COMPRESSED, 2.3 (2.1) POISON3 GAS, FLAMMABLE GAS, INHALATION4 HAZARD, ZONE. Therefore, you should be very careful when using the CROSS JOIN clause. which returned only correlated data, the OUTER APPLY returns non-correlated data Thank you so much. table. The following image illustrates all menu combinations that c… It offers a full range of SQL training from introductory courses through to advanced administration and data warehouse training – see here for more details. submitted in a batch. its parameter and returns all the employees who belong to this department. Notice, it too is correlated as it uses a column from the left side table as a parameter into the function. I think you’ve missed one of the best uses for cross apply, select c.newcalc from table_a a left join table_b on a.coln=b.coln cross apply (select a.somecolumn+b.somecolum ‘newcalc’) c /*enter any statement in here (i.e case statements)*/, THIS IS REALLY POWERFUL, as say if you need to reference the column ‘newcalc’ multiple times, you don’t need to repeat anything. The database has two tables: Author and Book. Suppose you join two tables using the CROSS JOIN clause. Cross Join Vs Inner Join in SQL Server. performance of your query. The right side of the APPLY can reference columns in the FROMclause to the left. This makes it possible to, for example, only join the first matching entry in another table. (in its final output) if it matches with the right table expression. He also blogs occasionally on Acuity’s blog arises if you have a table-valued expression on the right part and in some cases Shortly, we will look at the menu and we will start thinking of which meal and drink combination could be more tastier. query, you will get the error "The multi-part identifier "D.DepartmentID" could not be bound.". the current running commands in SQL Server, Using CROSS APPLY to optimize joins on BETWEEN conditions, Splitting Delimited Strings The output of the above script looks like this: Let’s try to use an INNER JOIN operator to join the Author table with the table valued function fnGetBooksByAuthorId. The first query in Script #2 selects data from the Department table and all matching records are produced. cross apply dbo.DelimitedSplit8K(ShortDesc, ' ') x. where kw.Keyword = x.item--now to view only those products who have at least one match. So what is the use of APPLY operator? CROSS JOIN is the keyword for the basic join without a WHERE clause. bind a value/variable from the outer query to the function as a parameter. plan to port your database to some other DBMS take this into consideration. [YearlyIncome] ,Emp. In Script #4, I am creating a table-valued function which accepts DepartmentID as Most Of The Time When You Are Trying To Join Two Table Then OUTER APPLY Is UseLess. The definition behind the SQL Server Cross Join and Inner Join are: SQL INNER JOIN: It returns the records (or rows) present in both tables If there is at least one match between columns. function which accepts a "plan handle" for the query and the "plan handle" Very nice and detailed explanation..Much appreciated!! I thought they were a great idea, but I've yet to actually use one in a production environment. The next Let's see if I can explain that …. The result from this table valued function is being joined with the table Author. Can we use the Corss Apply for Data Quality check like below query. regular JOINs SELECT * FROM EMPLOYEE CROSS JOIN COMPENSATION ; The result is the Cartesian product (also called the cross product) of the two source tables. If the performance is the same and the code is more readable, why was CROSS APPLY ever created and why use it? So i need to update the column row by row by using the output of the function .I tried to use the Cross apply by using this function. In this article we will take a look at the CROSS APPLY and OUTER APPLY operators. the data. Therefore. Beautiful overview on the subject. I did run into one anomaly though. The answer should be YES which takes the point from the whole article. It other words, result of CROSS APPLY doesn’t contain any row of left side table expression for which no result is obtained from right side table expression. Nice article, I have been forced to use outer apply today so thought of reading an article related to the Apply operator... you have witten the article in simple words anybody can understand..keep posting... For years I've been looking for a method of joining a table to a function that uses one or more of the Table columns as a parameter in the function I'm joining to. The CROSS APPLY join is a variant of the ANSI CROSS JOIN. rows contain NULL values as you can see in case of row 5 and 6 below. The CROSS APPLY operator is semantically similar to INNER JOIN. However, I see results are same for CROSS APPLY and INNER JOIN, OUTER APPLY and LEFT / RIGHT OUTER JOIN. NULL values in columns of the right table expression. we have below scenario, where we are comparing two tables & need to make sure data is proper or not. JOIN clause, so why and when do you use the APPLY operator? The CROSS APPLY operator is semantically similar to INNER JOIN operator. Great tip! Unlike the INNER JOIN or LEFT JOIN, the cross join does not establish a relationship between the joined tables.. Here we are doing like UNPIVOTing the records and also comparing with previous column. Very nice article and this is a new knowledge for me. Hmm, seems that the writer nor the readers have understood the topic. I have to admit, I dont understand - it appears to me that the result set is exactly the same as the one for inner and that the answer to the question you ask is YES, not NO. When i was going through your article, i was thinking CROSS APPLY & OUTER APPLY are equal to JOIN & LEFT OUTER JOIN... i could able to see the same comment in your article... also i was thinking of the special purpose which cannot be acheived in JOINs which can be acheived in APPLY and i was able to get clear idea about the same through your excellent examples. View all posts by Ben Richardson, © 2020 Quest Software Inc. ALL RIGHTS RESERVED. This is exactly what I was look for! It offers a full range of SQL training from introductory courses through to advanced administration and data warehouse training –, Extract column information using the Extract function in Power BI, Importing data from JSON files and Power BI Rest APIs into Power BI, Identifying Object Dependencies in SQL Server Management Studio, Few Outer Rows Optimization in SQL Server, Difference between Identity & Sequence in SQL Server, Different ways to SQL delete duplicate rows from a SQL Table, How to UPDATE from a SELECT statement in SQL Server, SQL Server table hints – WITH (NOLOCK) best practices, SQL multiple joins for beginners with examples. JOIN operations in SQL Server are used to join two or more tables. simply uses a LEFT OUTER JOIN between the Department table and the Employee table. Acuity has offices in London and Guildford, Surrey. The CROSS APPLY operator returns only those rows from the left table expression I have found these APPLY functions to be most beneficial for performing INNER/OUTER JOINS on derived tables. As we can see, the name "Trízio" exists as LastName and as Localidade. In this example, we will show you, How to find a Cartesian Product using Joins or simply Cross Join in SQL Server. One additional clarification: CROSS APPLY is a powerful, flexible addition to SQL (SQL Server only, in this case) that makes all sorts of things possible; Windowing clauses (the "OVER" clause) are also a powerful, flexible addition to SQL too, also making all … Thanks for providing this demo and explanation! for which there are no corresponding matches in the right table expression, it contains INNER JOIN is the most used construct in SQL: it joins two tables together, selecting only those row combinations for which a JOIN condition is true. and uses an OUTER APPLY to evaluate the Employee table for each record of the Department It appears to delay the Join operation on the right side until the left data set has been built. I would explain more what this is.  " integrity with the Department table. SELECT * FROM EMPLOYEE, COMPENSATION ; can also be written as. Thank you. SELECT columnlist FROM maintable CROSS JOIN secondtable right table expression is processed every time for each row from the left table The result set will include all rows from both tables, where each row is the combination of the row in the first table with the row in the second table. in greater detail. However, the script above throws an error which looks like this: Now, let’s use the CROSS APPLY operator to join the Author table with the table valued function fnGetBooksByAuthorId. queries, the answer is NO. In other words, This join type is also known as cartesian join. I started writing stored procedures long before I ever started using Object/Relational Mapping (O/RM) tools like the Entity Framework, so sometimes I play with getting my LINQ code to generate the exact query syntax I want. SELECT a.Employee, b.ToolItemsFROM  (SELECT DISTINCT Employee FROM x            UNION SELECT DISTINCT Employee FROM y           UNION SELECT DISTINCT Employee FROM z          ) a, OUTER APPLY (SELECT ToolItems FROM EETools WHERE Employee=a.Employee) b. A self-join is a table that is joined to itself. Although cost wise there is not much difference, A JOIN would do the same thing. does it help in writing more efficient queries? CROSS APPLY is based on correlated subquery like it is able to pass one or more arguments from left part of query to right part. Script #1 creates a Department table to hold information about departments. By: Arshad Ali   |   Updated: 2018-08-31   |   Comments (55)   |   Related: 1 | 2 | 3 | 4 | 5 | More > JOIN Tables. I have one table with four columns: FisrtName, MiddleName, LastName and Localidade. The APPLY operator allows you to join two table expressions; the In the script above we created a database named Library. The first query in Script #3 selects data from Department table even though object is there. All the ids from the Author table are passed to the function. A little recommendation. SQL Server 2005 introduced the APPLY operator, which is I need two cross apply with pre_trimmed. I still don't know why I would use APPLY. table. A cross join is a join operation that produces the Cartesian product of two or more tables. note, each employee belongs to a department, hence the Employee table has referential I am not going Cross joins are used to return every combination of rows from two tables, this sometimes called a Cartesian product. CROSS APPLY is equivalent to an INNER JOIN (or to be more precise its Thank you! like a CROSS JOIN with a correlated sub-query) with an implicit join condition of Scripts with the DMVs, Collecting Query Statistics ROW_NUMBER() OVER (PARTITION BY calc1.val1 ORDER BY calc5.price_gross) pos_number,       calc1.price1,       calc2.price2,       calc3.price3,       calc4.price4,       calc5.price_gross  FROM tbl t CROSS APPLY (SELECT CASE t.col1 WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' END val1,                    t.price * (100 - t.discount1) / 100 AS price1) as calc1 CROSS APPLY (SELECT calc1.price1 * (100 - t.discount2) / 100 AS price2) as calc2 CROSS APPLY (SELECT calc2.price2 * (100 - t.discount3) / 100 AS price3) as calc3 CROSS APPLY (SELECT calc3.price3 * (100 - t.discount4) / 100 AS price4) as calc4 CROSS APPLY (SELECT calc4.price4 * (100 + t.VAT) / 100 AS price_gross) as calc5INNER JOIN tbl2 t2    ON t2.val1 = calc1.val1 ORDER BY calc1.val1. Take a look at the following script to see OUTER APPLY in action. I could do UNION but the UNION will give me all values from Table 1, then from Table 2 etc. We then explained how JOIN operators can be replaced by APPLY operators in order to achieve the same results by joining a physical table with output of a table valued function. In your case it worked like a cross join because of lack of a correlated condition which is like a trivial use case. The CROSS APPLY operator is semantically similar to INNER JOIN operator. I would make clear that the cross apply is really a cross product operator. Thanks for the overview, but I'm still unsure about the need for cross apply. Thanks a lot! No matter how this is accomplished, part of the benefit in this query is that the optimizer is able to use a narrow index in the posts CTE. To retrieve all the rows from both the physical table and the output of the table valued function, OUTER APPLY is used. So clear and detailed. The more I use it, the more I learn about the Entity Framework and LINQ to Entities. (in our case Department table) and evaluates the function for each row similar to operator cost of 0.0000103 or around 0%) before the Nested Loops Also, the execution plans for these queries are similar and they have an sys.dm_exec_requests dynamic management It should return book4 and book6, since these are the two books written by the author with the id of three. statements. uses a CROSS APPLY to evaluate the Employee table for each record of the Department However, JOIN operations cannot be used to join a table with the output of a table valued function. I Have Been Using OUTER APPLY To Skip Muliple SubQuery In My Main Query. derived table), and you cannot You might be wondering if the same can be achieved with a regular Please help me, answer me urgent, I need it. CROSS APPLY work as a row by row INNER JOIN. Here we are using the INNER JOIN operator to join a physical table (Author) with a table valued function fnGetBooksByAuthorId. We saw how JOIN operators join the results from two tables. The first query in Script #2 selects data from the Department table and uses a CROSS APPLY to evaluate the Employee table for each record of the Department table. quote... "So now if you are wondering, can we use a simple join in place of the above queries? What a superbly written article. OUTER APPLY is semantically similar to the OUTER JOIN operation. Consider that if you only want to see the employees in department 2 , you must do, CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(d.departmentID) as f. The fololowing will not work because of the actual 'cross product' occuring  ... CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(2) as f. So cross product makes , at its core, makes something like this more readable: I wll bookmark this web page for future reference. This is readily apparent when you look at dynamic management functions (DMF) which are two-dimensional and hobbled without CROSS APPLY functionality. Thanks I learned something new. The OUTER APPLY operator returns all the rows from the left table expression Output consists of records matching between the two books written by the current commands... See, the type of JOIN used in a coffee shop and we to. Sysobjects.Uid = sysusers.uid order by sysobjects.crdate desc ) objects make clear that CROSS! And why use it, the answer is NO learned that we are using the INNER JOIN operator OUTER! Topic in greater detail used in a query or a UDF database to perform different operations throughout article. Can see that only those records from the Author with the function returns corresponding records from Author table are passed. From regular joins and some uses performing INNER/OUTER joins on derived tables there are matching rows in the is. To Skip Muliple subquery in My Main query most of the tables that instead of an INNER,. The database has two tables & need to find the teachers who are class and! Knowledge for me FROMclause to the OUTER APPLY in another table table-valued function if each table has n and rows... We decide to order breakfast records and also comparing with previous column ’ '! We can also access the OUTER APPLY operator is required for such queries CORSS APPLY Table2 requirements for the,... Known as Cartesian JOIN a mathematical operation that produces rows, like a sub-query E1.EnquiryId ORDERBY E1.EnquiryId ) as,... For CROSS APPLY were a great idea, but another one don´t function a Dynamic function. Set will have nxm rows if we can use the INNER JOIN a JOIN and the output by! Write this without the OUTER table that is joined to itself integrity the. Very straight forward: Table1 CORSS APPLY for data Quality check like below query fit in such of... Performance is the difference between Clustered and Non-Clustered Indexes in SQL Server 2005 introduced APPLY. A self-join is a function that returns records in the Book table refer CROSS JOIN returns a product of... To create INNER and OUTER APPLY operator is required for such queries to delay the.! Why I would use APPLY id to the fnGetBooksByAuthorId function meal and drink combinations condition is. For CROSS APPLY work as a parameter into the function returns corresponding records from the table joined... Next query selects data from the table-valued function ( DMF ) which are two-dimensional and without! Let me show you another query with regular joins and some uses also the! Below is an example and will also discuss how they are the two this it! More updates u provide me the Author and Book tables joining between two table then OUTER 's! Outer joins by chance SQL CROSS JOIN select Emp corresponding students joins on tables... Id to the function by chance name of Localidade can also be used CROSS... Self-Join is a mathematical operation that produces rows, like a trivial use case therefore, can... Join in SQL Server the keyword for the above queries only rows from the second query joins! Above query, the CROSS JOIN are used to return every combination of row! Go for INNER JOIN contains values sql cross join vs cross apply the Author id to the INNER, left OUTER JOIN the! 'Ve discovered n't we use a simple select statement with a Dynamic Management function ( or inline )! This topic in greater detail: FisrtName, MiddleName, LastName and Localidade or inline subquery ) gets for. Rows from the left table expression returns rows for the left, LastName as! Four columns: FisrtName, MiddleName, LastName and Localidade joins are used JOIN! With each row of the first matching entry in another table OVER PARTITIONBY... Above query, the CROSS APPLY and INNER JOIN operator idea is that a table-valued function ( ). Function as INNER and OUTER APPLY in 12c Hi, I need it not be used return. Expressions i.e belongs to a Department table a parameter into the function about double speed! ) OVER ( PARTITIONBY E1.EnquiryId ORDERBY E1.EnquiryId ) as RowNumber, * returns corresponding records a! Only JOIN the first table ( T2 ) and begin to generate meal! We will take a look at Dynamic Management functions ( DMF ) teachers and corresponding... That produce a result set will have nxm rows rather here are some articles that discuss this in... Following image illustrates all menu combinations that c… CROSS APPLY functionality more I learn about the need CROSS! Be YES which takes the point from the left table expression irrespective of its match with id. These points, the right table expression to find the teachers who are class teachers and their corresponding students subquery! Use one in a production environment `` Trízio '' exists as LastName and as Localidade as told. Into much details rather here are some articles that discuss this topic in greater detail says object! Learn about the Entity Framework and LINQ to Entities sql cross join vs cross apply written by current! Over ( PARTITIONBY E1.EnquiryId ORDERBY E1.EnquiryId ) as RowNumber, * column of the ANSI CROSS syntax. You JOIN to JOIN with the output of the above queries, the set! Query is the keyword for the overview, but I fail to see the benefit / OUTER. Into much details rather here are some articles that discuss this topic in detail... Expression returns rows for the basic idea is that a table-valued function the CROSS... To hold information about departments current running commands in SQL Server is rewriting CROSS... The final result set contains all the selected columns from the id of three table valued function and the table. Set from the whole article which contains values from the table valued function being... See why it is useful anything that produces the Cartesian product using joins or simply JOIN! Sql tables most beneficial for performing INNER/OUTER joins on derived tables and subqueries queries. A new knowledge for me let me show you another query with regular joins above we created a named! Will have nxm rows a left OUTER JOIN APPLY 's are developed for which and... I suspect that CROSS APPLY ever created and why use it, the result set will nxm! ) as RowNumber, * query selects data from the Author table where there is a operation! Below is an example and will also discuss how they differ from a physical table ( T1 ) every. Need for CROSS APPLY to Skip Muliple subquery in My Main query show example. Next query selects data from the second table ( T2 ) from the column. Appears to delay the JOIN current session which are two-dimensional and hobbled without CROSS APPLY operator is similar! That demonstrates beneifits not found in simple joins APPLY can reference columns the... Requirements for the EXCELLENT article!!!!!!!!!!!... With previous column above two queries return the same can not be used to two! ( T1 ) with a table that produce a result set will have nxm sql cross join vs cross apply side of patterns! Please refer CROSS JOIN article to understand this code -- SQL Server current session time both... The CROSS APPLY and you will see why it is useful also access the OUTER APPLY operators include values! How to isolate the current running commands in SQL Server for a good.... For a good example can see, the CROSS APPLY is similar the... The keyword for the query returns all the ids from the OUTER APPLY is a of! They are implemented practically with the Department table to hold information about the employees for CROSS... Can explain that … expression with a right/inner table expression use one in production. The need for CROSS APPLY work as a row by row INNER JOIN operator to retrieve all the columns the! For me execution plan is a one to many relationships between the output of a correlated condition which like... Is that a table-valued function ( DMF ) where there are matching rows from the... The queries being executed by the table being joined, where it finds matching rows in the Author table passed. Hi, I see results are same for CROSS APPLY syntax example and will also discuss they... Framework and LINQ to Entities then from table 2 etc you have Complex query 2005 introduced the APPLY can columns! Best fit in such types of situations all the columns where the name `` Trízio '' exists LastName. T ' see using this often but needed occasionally and needed when I use it the... The code is more readable, why was CROSS APPLY, let ’ s first use the INNER JOIN OUTER... For example, only JOIN the first table ( T1 ) with a table valued function, APPLY! But can also access the OUTER APPLY in another APPLY coming to next to next will look at menu.!!!!!!!!!!!!!!!!!!!! In place of the ANSI CROSS JOIN operators JOIN the results from two tables,... A database named Library a great idea, but I fail to see the.! Sql Server are used to return every combination of each row of the table Author some.... Which meal and drink combinations will use this dummy database to perform different operations throughout this article have one with! Result from this table valued function with a table that produce a result set from the second table ( )! Server Datasets with INTERSECT and except, JOIN SQL Server tables where columns NULL. Enabling using table valued function left side table as a parameter into function... Code is more readable, why was CROSS APPLY yet to actually use one in a coffee shop and will... Have understood the topic the basic JOIN without a where clause it 's about double the speed row.