none
can cross join be used with other joins

    Question

  • hi,

    Q1) is this syntactically correct , cross join with other joins like inner outer. like following.

    select * from t1 cross join t2
    inner join t3 on cast(t3.c1 as varchar) =  (cast(t1.id as varchar) +  cast(t2.t2 as varchar))

    I have seen it works but wanted to know is it syntactically correct.

    yours sincerely

    Sunday, July 27, 2014 5:47 AM

Answers

  • Yes, you can mix CROSS JOIN with the other joins. Use parentheses to make the logical join order clear.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by rajemessage Tuesday, July 29, 2014 4:43 AM
    Sunday, July 27, 2014 10:20 AM
  • Hi

    First, Sorry, I wanted to VOTE (as I wrote) and not to Propose as answer :-)

    Join are not necessarily executing in the order we write them. I can show simple example with tables that include the amount of rows is very different.

    /**************************************************** DDL - Create tables */
    CREATE TABLE T1 (
    	ID INT IDENTITY CONSTRAINT PK_T1 PRIMARY KEY,
    	MyValue UNIQUEIDENTIFIER DEFAULT NEWID()
    )
    CREATE TABLE T2 (
    	ID INT IDENTITY CONSTRAINT PK_T2 PRIMARY KEY,
    	MyValue UNIQUEIDENTIFIER DEFAULT NEWID()
    )
    CREATE TABLE T3 (
    	ID INT IDENTITY CONSTRAINT PK_T3 PRIMARY KEY,
    	MyValue UNIQUEIDENTIFIER DEFAULT NEWID()
    )
    GO
    
    /**************************************************** DML - Populate Tables*/
    SET NOCOUNT ON;
    
    insert T1 (MyValue)
    select top 100 null
    from _ArielyAccessoriesDB.dbo.ArielyNumbers
    GO
     
    insert T2 (MyValue)
    select top 500 null
    from _ArielyAccessoriesDB.dbo.ArielyNumbers
    GO
     
    insert T3 (MyValue)
    select top 10000 null
    from _ArielyAccessoriesDB.dbo.ArielyNumbers
    GO
    
    /**************************************************** Play Time */
    select T1.MyValue, T2.MyValue, T3.MyValue
    from T3
        join T2 on T2.ID = T3.ID
        join T1 on T1.ID = T2.ID
    GO
    -- Check Execution Plan [EP]
    -- Notice that SQL Server has changed the join order from T3-T2-T1 to T1-T2-T3 because it’s better that way.
    -- You can notice that the order was by the number of rowns in tables from the smallest to the bigest SET
    

     hope this is helpful :-)


    [Personal Site] [Blog] [Facebook]signature

    • Marked as answer by rajemessage Tuesday, July 29, 2014 4:44 AM
    Sunday, July 27, 2014 8:19 PM

All replies

  • Its syntactically correct

    what it will do is take a cartesian product between t1 and t2 and then to result does the join to t3 on specified condition

     One thing you need to note is to always specify a length while casting to varchar

    see

    http://visakhm.blogspot.in/2010/02/importance-of-specifying-length-in.html


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, July 27, 2014 6:30 AM
  • Yes, you can mix CROSS JOIN with the other joins. Use parentheses to make the logical join order clear.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by rajemessage Tuesday, July 29, 2014 4:43 AM
    Sunday, July 27, 2014 10:20 AM
  • pls tel me i am correct, (i have put the brackes)

    select * from (t1 cross join t2)
    join t3 on cast(t3.c1 as varchar) =  (cast(t1.id as varchar) +  cast(t2.t2 as varchar))

    yours sincerly

    Sunday, July 27, 2014 11:26 AM
  • Rather than asking, why not test?

    Specifically, I cannot say what is correct, because I don't know what result you think it is correct.

    (Actually, I'm not sure that parens are needed. That is

       (A CROSS JOIN B) JOIN C

    may be the same as

       A CROSS JOIN (B JOIN C)

    but I am not sure - this is not an extremely common thing to do.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, July 27, 2014 11:58 AM
  • JOINs are done in left to right order. The ON clause associates with the nearest JOIN. If you want to be safe, use parentheses. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, July 27, 2014 2:27 PM
  • mmmm....

    Short answer, direct approach, and beneficial, no swearing and rudeness, ISO Standard not mentioned... I think someone hacked CELKO's account :-)

    If I may, Well done Celko :-) 
    If this approach is the new road, then I will vote for you.

    [Personal Site] [Blog] [Facebook]signature

    Sunday, July 27, 2014 6:40 PM
  • Hi

    First, Sorry, I wanted to VOTE (as I wrote) and not to Propose as answer :-)

    Join are not necessarily executing in the order we write them. I can show simple example with tables that include the amount of rows is very different.

    /**************************************************** DDL - Create tables */
    CREATE TABLE T1 (
    	ID INT IDENTITY CONSTRAINT PK_T1 PRIMARY KEY,
    	MyValue UNIQUEIDENTIFIER DEFAULT NEWID()
    )
    CREATE TABLE T2 (
    	ID INT IDENTITY CONSTRAINT PK_T2 PRIMARY KEY,
    	MyValue UNIQUEIDENTIFIER DEFAULT NEWID()
    )
    CREATE TABLE T3 (
    	ID INT IDENTITY CONSTRAINT PK_T3 PRIMARY KEY,
    	MyValue UNIQUEIDENTIFIER DEFAULT NEWID()
    )
    GO
    
    /**************************************************** DML - Populate Tables*/
    SET NOCOUNT ON;
    
    insert T1 (MyValue)
    select top 100 null
    from _ArielyAccessoriesDB.dbo.ArielyNumbers
    GO
     
    insert T2 (MyValue)
    select top 500 null
    from _ArielyAccessoriesDB.dbo.ArielyNumbers
    GO
     
    insert T3 (MyValue)
    select top 10000 null
    from _ArielyAccessoriesDB.dbo.ArielyNumbers
    GO
    
    /**************************************************** Play Time */
    select T1.MyValue, T2.MyValue, T3.MyValue
    from T3
        join T2 on T2.ID = T3.ID
        join T1 on T1.ID = T2.ID
    GO
    -- Check Execution Plan [EP]
    -- Notice that SQL Server has changed the join order from T3-T2-T1 to T1-T2-T3 because it’s better that way.
    -- You can notice that the order was by the number of rowns in tables from the smallest to the bigest SET
    

     hope this is helpful :-)


    [Personal Site] [Blog] [Facebook]signature

    • Marked as answer by rajemessage Tuesday, July 29, 2014 4:44 AM
    Sunday, July 27, 2014 8:19 PM