Difference between inner join and cross join?

  • Can anybody tell me the Difference between inner join and cross join.

    how and when we have to use them?

    _____________________________________________________________________________________________________________
    Paresh Prajapati
    ➡ +919924626601
    http://paresh-sqldba.blogspot.com/[/url]
    LinkedIn | Tweet Me | FaceBook | Brijj

  • Dude...I would suggest you to go through the BOL and then come up with your doubts

    -Vikas Bindra

  • INNER Join you use if there's a one-to-one relationship between two tables. A CROSS JOIN is the toal opposite of an INNER JOIN .

    The example should make help to make it clear:

    CREATE Table t1

    (c1 int, c2 int)

    GO

    CREATE Table t2

    (c1 int, c2 varchar(10))

    GO

    INSERT INTO t1

    SELECT 1,1

    UNION

    SELECT 2,2

    UNION

    SELECT 3,1

    UNION

    SELECT 4,2

    ;

    INSERT INTO t2

    SELECT 1,'Mike'

    UNION

    SELECT 1,'John'

    UNION

    SELECT 3,'Harry'

    UNION

    SELECT 4,'Bob';

    SELECT t1.c1,t2.c2

    FROM t1 INNER JOIN t2

    ON t1.c1 = t2.c1

    SELECT t1.c1,t2.c2

    FROM t1 CROSS JOIN t2

    DROP TABLE t1,t2

    [font="Verdana"]Markus Bohse[/font]

  • In simple words...

    Inner Join returns matching records from two or more tables.

    Cross Join --Returns carteian result. E.g tableA contains 2 record, tableB contains 10 records. using cross join the result will be 2 x 10 = 20 records.

    for more information you can check the BOL.

    Abhijit - http://abhijitmore.wordpress.com

  • Paresh Prajapati (1/16/2009)


    Can anybody tell me the Difference between inner join and cross join.

    how and when we have to use them?

    http://en.wikipedia.org/wiki/JOIN%5B/url%5D

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • How and when to use them?

    Inner join uses a column on first table to match rows in the second table.

    eg

    Select * from Students,Teachers where Students.TeacherID=Teachers.ID

    This is inner join we use it commonly

    Now omit the where clause

    Select * from Students , Teachers

    The result will be number of rows in Students * rows in Teachers

    Cross joins are used to identify all possible combinations for a particular domain

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Hi,

    The Wikipedia article is also quite good for determining the differences between the various join types quickly. It also includes examples and source code.

    http://en.wikipedia.org/wiki/Join_(SQL)

    Cheers,

  • BigJohn (1/16/2009)


    Hi,

    The Wikipedia article is also quite good for determining the differences between the various join types quickly. It also includes examples and source code.

    http://en.wikipedia.org/wiki/Join_(SQL)

    Cheers,

    Yes it is true like few minutes ago here...http://www.sqlservercentral.com/Forums/FindPost637836.aspx

    ============================================================
    SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
    http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

  • Here is the difference:

    Inner Join: SQL Inner Join or Equi Join is the most simple join where all rows from the intended tables are cached together if they meet the stated condition. Two or more tables are required for this join. Inner Join can be used with various SQL conditional statements like WHERE, GROUP BY, ORDER BY, etc.

    Syntax

    SELECT column-name 
    FROM table-1 INNER JOIN table-2
    WHERE table-1.column-name = table-2.column-name;

    to learn more about the query and problem statement of inner join check this out: Click here

    Cross Join: Cross Join is useful when you need all combinations. It maps each row of the first table with all the rows from the second table. Cross Join is also called Cartesian Product – if the first table has X rows and the second table has Y rows, it will return X*Y rows.

    Syntax of SQL Cross Join

    There are two ways to implement CROSS JOIN in SQL.

    CROSS JOIN clause

    SELECT [COLUMNS] FROM [TABLE_1] CROSS JOIN [TABLE_2]

     

    FROM clause without WHERE clause

    SELECT [COLUMNS] FROM [TABLE_1],[TABLE_2]

    To learn more about Cross Join in SQL: Click here

  • Hi Tony, and welcome aboard!

    Just to add to your explanation, a CROSS JOIN can also be used as a massive, super fast  "Pseudo Cursor" to replace <<insert drum roll here>> many forms of RBAR (While loops, cursors, recursive CTEs, etc) and it doesn't even read any data from either table.  It just uses the "presence of rows" to fire up the underlying loops present in the "macro" that everyone knows in SQL Server... The SELECT command.

    It's the basis of Itzik Ben-Gan's infamous "GetNums" function and my "fnTally" function for generating sequences of numbers and much more in a nasty fast way and is the basis for some incredibly fast string splitters, decoder and encoder functions, generation of massive amounts of randomized but constrained test data, relational multiplication of rows, and a whole bunch more.  As a result, I'd have to say that I end up implicitly using some form of Cross Join more often than any other form of join other than Inner Joins.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • A cross join matches all rows in one table to all rows in another table. An inner join matches on a field or fields. If you have one table with 10 rows and another with 10 rows then the two joins will behave differently

    Inner Join combines the two or more records but displays only matching values in both tables. Inner join applies only the specified columns. Cross join defines as a Cartesian product where the number of rows in the first table multiplied by the number of rows in the second table.

    Check more American Airlines Policies
    https://airtravelpolicy.com/american-airlines-cancellation-policy-fee/
    https://airtravelpolicy.com/american-airlines-change-flight-policy/

    https://airtravelpolicy.com/reservations-with-american-airlines-book-a-flight/
    https://airtravelpolicy.com/how-to-change-flight-date-on-american-airlines/
    https://airtravelpolicy.com/how-to-rebook-american-airlines-flight/
    https://airtravelpolicy.com/american-airlines-name-change-policy-fee-and-process/
    https://airtravelpolicy.com/what-to-do-when-i-miss-my-flight-american-airlines/
    https://airtravelpolicy.com/american-airlines-pet-policy-fee-guide-to-aa-pet-travel-policies/

  • All true, but it's equally true that you can turn a cross join into what is in reallity an inner join by moving the ON join criteria of an INNER JOIN to the WHERE part.

    So these three queries are really the same and will produce the same result:

    SELECT a.id AS a_id, b.id AS b_id
    FROM table1 a
    INNER JOIN table2 b ON a.id=b.id;
    SELECT a.id AS a_id, b.id AS b_id
    FROM table1 a, table2 b
    WHERE a.id=b.id;
    SELECT a.id AS a_id, b.id AS b_id
    FROM table1 a
    CROSS JOIN table2 b
    WHERE a.id=b.id;

    However, using a cross join in such a way is nowadays considered old-fashioned and backwards. It was, however, how it would have been done before the concepts of INNER and OUTER JOINs entered the SQL standard.

    So if you see it used like that in some old text books, you need to know that even though it will still work, it is not something you should use. An INNER JOIN with the join criteria in the ON section is much clearer.

     

    • This reply was modified 1 month ago by  kaj. Reason: Moved SQL into the correct format for readability purposes
  • All true and thanks for posting the examples.

    Just to be sure though (for any newbies that might be reading this), that's not what I was talking about in my previous post.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply