none
How to avoid cross joins RRS feed

  • Question

  • Hi 

    In my query I am trying to join on both month and year to join with another table. But this is creating cross joins and taking long time to run the query. Is there any way to avoid this problem ?

    Thanks in advance



    Tuesday, October 15, 2019 4:37 AM

All replies

  • Hi,

    Use join(INNER/LEFT/RIGHT based on your requirement) with 2 multiple ON conditions.

    Can you please attach your CREATE TABLE/INSERT INTO Scripts with some sample inputs and show your expected output.So that we can travel in right direction and find the solution soon.

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Tuesday, October 15, 2019 4:43 AM
  • Hi  AlwaysLearner08,

    Could you  please share us your table structure (CREATE TABLE …) and some sample data (INSERT INTO…) along with your expected result? So that we’ll get a right direction and make some test.

    Also I will provide you some example to understand JOIN.

    --drop table a 
    --drop table b
    create table A
    (id int,
    name varchar(20))
    create table B
    (id int,
    job int,
    parent_id int)
     insert into A values 
     (1,'sam'),
     (2,'lily'),
     (3,'moon')
      insert into B values 
     (1,23,1),
     (2,34,2),
     (3,34,4)
     ------INNER JOIN
    select a.*,b.* from a INNER JOIN  b on a.id=b.parent_id
    /*
    id          name                 id          job         parent_id
    ----------- -------------------- ----------- ----------- -----------
    1           sam                  1           23          1
    2           lily                 2           34          2
    */
    ------LEFT OUTER JOIN
     select a.*,b.* from a LEFT OUTER JOIN  b on a.id=b.parent_id
     /*
    id          name                 id          job         parent_id
    ----------- -------------------- ----------- ----------- -----------
    1           sam                  1           23          1
    2           lily                 2           34          2
    3           moon                 NULL        NULL        NULL
     */
     ------RIGHT OUTER JOIN
     select a.*,b.* from a RIGHT OUTER JOIN  b on a.id=b.parent_id
     /*
    id          name                 id          job         parent_id
    ----------- -------------------- ----------- ----------- -----------
    1           sam                  1           23          1
    2           lily                 2           34          2
    NULL        NULL                 3           34          4
     */
      ------FULL OUTER JOIN
     select a.*,b.* from a FULL OUTER JOIN  b on a.id=b.parent_id
     /*
    id          name                 id          job         parent_id
    ----------- -------------------- ----------- ----------- -----------
    1           sam                  1           23          1
    2           lily                 2           34          2
    3           moon                 NULL        NULL        NULL
    NULL        NULL                 3           34          4
     */
     ---cross join 
      select a.*,b.* from a cross join   b 
      /*
      id          name                 id          job         parent_id
    ----------- -------------------- ----------- ----------- -----------
    1           sam                  1           23          1
    2           lily                 1           23          1
    3           moon                 1           23          1
    1           sam                  2           34          2
    2           lily                 2           34          2
    3           moon                 2           34          2
    1           sam                  3           34          4
    2           lily                 3           34          4
    3           moon                 3           34          4
      */
    

    Hope it will help you.

    Best Regards,

    Rachel



    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 15, 2019 7:06 AM
  • But this is creating cross joins and taking long time

    Cross join? Or do you mean a cartesian product? https://en.wikipedia.org/wiki/Cartesian_product

    Then your JOIN condition is wrong/not complete, but we don't know your table design & query.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, October 15, 2019 7:32 AM
  • Hi 

    Yeah, when I mean Cross Join it is Cartesian product. 

    Tuesday, October 15, 2019 6:20 PM
  • Your query in question?
    Tuesday, October 15, 2019 6:28 PM
    Moderator
  • Hi,

    Can you attach your script that you have tried with example and expected result?

    Regards,
    Arulmouzhi

    Tuesday, October 15, 2019 6:28 PM
  • Hi,

    Attached 1 small example to avoid cross join (as mentioned in title)-

    IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
    	DROP TABLE #TEMP;
    IF OBJECT_ID('TEMPDB..#TEMP1') IS NOT NULL
    	DROP TABLE #TEMP1;
    
    CREATE TABLE #TEMP(ID INT NOT NULL);
    GO
    INSERT INTO #TEMP(ID) VALUES (1),(2),(3);
    GO
    
    CREATE TABLE #TEMP1(ID INT NOT NULL);
    GO
    INSERT INTO #TEMP1(ID) VALUES (1),(2),(3);
    GO
    
    --CROSS JOIN QUERY (EXAMPLE)
    SELECT * FROM #TEMP CROSS JOIN #TEMP1; 
    	--CARTESIAN PRODUCT
    	--(3 ROWS FROM #TEMP GOT MULTIPLIED WITH 3 ROWS FROM 3TEMP1)
    
    --TO AVOID CROSS JOIN IN YOUR QUERY (EXAMPLE)
    SELECT * FROM #TEMP,#TEMP1; 
    	--SAME CARTESIAN PRODUCT
    	--(3 ROWS FROM #TEMP GOT MULTIPLIED WITH 3 ROWS FROM 3TEMP1)

    Note - Assumptions would lead to mistakes many times! If you want the right solution,please attach some inputs and expected output that you want without using cross join. 

    Please mark this as answer if it solved your issue. 
    Please vote this as helpful if it helps to solve your issue. 

    Thanks,
    Arulmouzhi

    Tuesday, October 15, 2019 7:50 PM
  • Could you  please share us your table structure (CREATE TABLE …) and some sample data (INSERT INTO…) along with your expected result? So that we’ll get a right direction and make some test.

    Best Regards,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 16, 2019 8:41 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 21, 2019 6:55 AM
  • Why did you fail to post any DDL? But a better question is why would anyone split a column (a date) into two fields (a month and year)? I happen to like the MySQL convention of using "yyyy-mm-00" for the interval month data type not supported by T-SQL. If you knew what you were doing this absurd problem would never happen. 

    --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

    Monday, October 21, 2019 8:48 PM