CROSS JOIN vs INNER JOIN in SQL

Question

What is the difference between CROSS JOIN and INNER JOIN   CROSS JOIN   SELECT      Movies CustomerID  Movies Movie  Customers Age       Customers Gender  Customers  Education Level        Customers  Internet Connection   Customers  Marital Status    FROM        Customers  CROSS JOIN      Movies   INNER JOIN   SELECT      Movies CustomerID  Movies Movie  Customers Age       Customers Gender  Customers  Education Level        Customers  Internet Connection   Customers  Marital Status  FROM        Customers  INNER JOIN      Movies ON Customers CustomerID   Movies CustomerID   Which one is better and why would I use either one

User · Answer

Here is the best example of Cross Join and Inner Join.

Consider the following tables

TABLE : Teacher

x------------------------x
| TchrId   | TeacherName | 
x----------|-------------x
|    T1    |    Mary     |
|    T2    |    Jim      |
x------------------------x

TABLE : Student

x--------------------------------------x
|  StudId  |    TchrId   | StudentName | 
x----------|-------------|-------------x            
|    S1    |     T1      |    Vineeth  |
|    S2    |     T1      |    Unni     |
x--------------------------------------x

1. INNER JOIN

Inner join selects the rows that satisfies both the table.

Consider we need to find the teachers who are class teachers and their corresponding students. In that condition, we need to apply JOIN or INNER JOIN and will

enter image description here

Query

SELECT T.TchrId,T.TeacherName,S.StudentName 
FROM #Teacher T
INNER JOIN #Student S ON T.TchrId = S.TchrId

SQL FIDDLE

Result

x--------------------------------------x
|  TchrId  | TeacherName | StudentName | 
x----------|-------------|-------------x            
|    T1    |     Mary    |    Vineeth  |
|    T1    |     Mary    |    Unni     |
x--------------------------------------x

2. CROSS JOIN

Cross join selects the all the rows from the first table and all the rows from second table and shows as Cartesian product ie, with all possibilities

Consider we need to find all the teachers in the school and students irrespective of class teachers, we need to apply CROSS JOIN.

enter image description here

Query

SELECT T.TchrId,T.TeacherName,S.StudentName 
FROM #Teacher T
CROSS JOIN #Student S

SQL FIDDLE

Result

x--------------------------------------x
|  TchrId  | TeacherName | StudentName | 
x----------|-------------|-------------x            
|    T2    |     Jim     |    Vineeth  |
|    T2    |     Jim     |    Unni     |
|    T1    |     Mary    |    Vineeth  |
|    T1    |     Mary    |    Unni     |
x--------------------------------------x

User · Answer

CROSS JOIN AThe CROSS JOIN is meant to generate a Cartesian Product  A Cartesian Product takes two sets A and B and generates all possible permutations of pair records from two given sets of data  For instance  assuming you have the following ranks and suits database tables   And the ranks has the following rows    name    symbol   rank value    ------- -------- ------------    Ace     A        14             King    K        13             Queen   Q        12             Jack    J        11             Ten     10       10             Nine    9         9            While the suits table contains the following records    name      symbol    --------- --------    Club                 Diamond              Heart                Spade               As CROSS JOIN query like the following one  SELECT    r symbol AS card rank     s symbol AS card suit FROM    ranks r CROSS JOIN    suits s  will generate all possible permutations of ranks and suites pairs    card rank   card suit    ----------- -----------    A                         A                         A                         A                         K                         K                         K                         K                         Q                         Q                         Q                         Q                         J                         J                         J                         J                         10                        10                        10                        10                        9                         9                         9                         9                        INNER JOIN On the other hand  INNER JOIN does not return the Cartesian Product of the two joining data sets  Instead  the INNER JOIN takes all elements from the left-side table and matches them against the records on the right-side table so that   if no record is matched on the right-side table  the left-side row is filtered out from the result set for any matching record on the right-side table  the left-side row is repeated as if there was a Cartesian Product between that record and all its associated child records on the right-side table   For instance  assuming we have a one-to-many table relationship between a parent post and a child post comment tables that look as follows   Now  if the post table has the following records    id   title        ---- -----------    1    Java          2    Hibernate     3    JPA          and the post comments table has these rows    id   review      post id    ---- ----------- ---------    1    Good        1           2    Excellent   1           3    Awesome     2          An INNER JOIN query like the following one  SELECT    p id AS post id     p title AS post title     pc review  AS review FROM post p INNER JOIN post comment pc ON pc post id   p id  is going to include all post records along with all their associated post comments    post id   post title   review       --------- ------------ -----------    1         Java         Good          1         Java         Excellent     2         Hibernate    Awesome       Basically  you can think of the INNER JOIN as a filtered CROSS JOIN where only the matching records are kept in the final result set

User · Answer

Please remember  if a WHERE clause is added  the cross join behaves as an inner join  For example  the following Transact-SQL queries produce the same result set   Please refer to http   technet microsoft com en-us library ms190690 v sql 105  aspx

User · Answer

A    1 5 3 4 6 7 9 8  B    2 8 5 4 3 6 9   cross join act as Cartesian product A   B    1 2    1 8      5 2    5 8   5 5       3 3      6 6      8 9  and returned this long result set    when processing inner join its done through Cartesian product and choose matching pairs   if think this ordered pairs as  two table s primary keys and on clause search for A   B then inner join choose  5 5    4 4    6 6    9 9  and returned asked column in select clause related to these id s   if cross join on a   b then it s result same result set as inner join  in that case also use inner join

User · Answer

Cross join and inner join are the same with the only difference that in inner join we booleanly filter some of the outcomes of the cartesian product  table1 x--------------------------------------x    fieldA       fieldB        fieldC      x---------- ------------- -------------x                  A            B           option1         A            B1          option2    x--------------------------------------x  table2 x--------------------------------------x    fieldA       fieldB        fieldC      x---------- ------------- -------------x                  A            B           optionB1        A1           B1          optionB2   x--------------------------------------x   cross join   A B option1 A B optionB1   A B option1 A1 B1 optionB2   A B1 option2 A B optionB1   A B1 option2 A1 B1 optionB2   inner join on field1  only with the value is the same in both tables    A B option1 A B optionB1   A B1 option2 A B optionB1   inner join on field1   A B option1 A B optionB1   It is on design of our data where we decide that there is only one case of the field we are using for the join  Join only cross join both tables and get only the lines accomplishing special boolean expression   Note that if the fields we are doing our Joins on would be null in both tables we would pass the filter  It is up to us or the database manufacturer to add extra rules to avoid or permit nulls  Adhering to the basics it is just a cross join followed by a filter

User · Answer

CROSS JOIN    INNER  JOIN   comma   quot   quot   TL DR The only difference between SQL CROSS JOIN   INNER  JOIN and comma   quot   quot    besides comma having lower precedence for evaluation order  is that  INNER  JOIN has an ON while CROSS JOIN and comma don t   Re intermediate products All three produce an intermediate conceptual SQL-style relational  quot Cartesian quot  product  aka cross join  of all possible combinations of a row from each table  It is ON and or WHERE that reduce the number of rows  SQL Fiddle The SQL Standard defines  lt comma gt  via product  7 5 1 b ii    lt cross join gt  aka CROSS JOIN via  lt comma gt   7 7 1 a  and  INNER  JOIN ON  lt search condition gt  via  lt comma gt  plus WHERE  7 7 1 b   As Wikipedia puts it   Cross join CROSS JOIN returns the Cartesian product of rows from tables in the join  In other words  it will produce rows which combine each row from the first table with each row from the second table    Inner join       The result of the join can be defined as the outcome of first taking the Cartesian product  or Cross join  of all records in the tables  combining every record in table A with every record in table B  and then returning all records which satisfy the join predicate    The  quot implicit join notation quot  simply lists the tables for joining  in the FROM clause of the SELECT statement  using commas to separate them  Thus it specifies a cross join  Re OUTER JOIN see my answer What is the difference between    INNER JOIN    and    OUTER JOIN      Re OUTER JOINs and using ON vs WHERE in them see my answer Conditions in LEFT JOIN  OUTER JOIN  vs INNER JOIN  Why compare columns between tables  When there are no duplicate rows  Every table holds the rows that make a true statement from a certain fill-in-the- named- blanks statement template   It makes a true proposition from--satisfies--a certain  characteristic  predicate    A base table holds the rows that make a true statement from some DBA-given statement template       rows where   customer C CustomerID has age C Age and            FROM Customers C   A join s intermediate product holds the rows that make a true statement from the AND of its operands  templates       rows where       customer C CustomerID has age C Age and       AND movie M Movie is rented by customer M CustomerID and            FROM Customers C CROSS JOIN Movies M   ON  amp  WHERE conditions are ANDed in to give a further template  The value is again the rows that satisfy that template       rows where       customer C CustomerID has age C Age and       AND movie M Movie is rented by customer M CustomerID and       AND C CustomerID   M CustomerID   AND C Age  gt   M  Minimum Age    AND C Age   18        FROM Customers C INNER JOIN Movies M   ON C CustomerID   M CustomerID   AND C Age  gt   M  Minimum Age    WHERE C Age   18    In particular  comparing columns for  SQL  equality between tables means that the rows kept from the product from the joined tables  parts of the template have the same  non-NULL  value for those columns  It s just coincidental that a lot of rows are typically removed by equality comparisons between tables--what is necessary and sufficient is to characterize the rows you want  Just write SQL for the template for the rows you want  Re the meaning of queries  and tables vs conditions  see  How to get matching data from another SQL table for two different columns  Inner Join and or Union  Is there any rule of thumb to construct SQL query from a human-readable description  Overloading  quot cross join quot  Unfortunately the term  quot cross join quot  gets used for   The intermediate product  CROSS JOIN   INNER  JOIN with an ON or WHERE that doesn t compare any columns from one table to any columns of another   Since that tends to return so many of the intermediate product rows    These various meanings get confounded   Eg as in other answers and comments here   Using CROSS JOIN vs  INNER  JOIN vs comma The common convention is   Use CROSS JOIN when and only when you don t compare columns between tables  That is to show that the lack of comparisons was intentional  Use  INNER  JOIN with ON when and only when you compare columns between tables   Plus possibly other conditions   Don t use comma   Typically also conditions not on pairs of tables are kept for a WHERE  But they may have to be put in a n INNER  JOIN ON to get appropriate rows for the argument to a RIGHT  LEFT or FULL  OUTER  JOIN  Re  quot Don t use comma quot  Mixing comma with explicit JOIN can mislead because comma has lower precedence  But given the role of the intermediate product in the meaning of CROSS JOIN   INNER  JOIN and comma  arguments for the convention above of not using it at all are shaky  A CROSS JOIN or comma is just like an  INNER  JOIN that s ON a TRUE condition  An intermediate product  ON and WHERE all introduce an AND in the corresponding predicate  However else INNER JOIN ON can be thought of--say  generating an output row only when finding a pair of input rows that satisfies the ON condition--it nevertheless returns the cross join rows that satisfy the condition  The only reason ON had to supplement comma in SQL was to write OUTER JOINs  Of course  an expression should make its meaning clear  but what is clear depends on what things are taken to mean  Re Venn diagrams A Venn diagram with two intersecting circles can illustrate the difference between output rows for INNER  LEFT  RIGHT  amp  FULL JOINs for the same input  And when the ON is unconditionally TRUE  the INNER JOIN result is the same as CROSS JOIN  Also it can illustrate the input and output rows for INTERSECT  UNION  amp  EXCEPT  And when both inputs have the same columns  the INTERSECT result is the same as for standard SQL NATURAL JOIN  and the EXCEPT result is the same as for certain idioms involving LEFT  amp  RIGHT JOIN  But it does not illustrate how  INNER  JOIN works in general  That just seems plausible at first glance  It can identify parts of input and or output for special cases of ON  PKs  primary keys   FKs  foreign keys  and or SELECT  All you have to do to see this is to identify what exactly are the elements of the sets represented by the circles   Which muddled presentations never make clear   Remember that in general for joins output rows have different headings from input rows  And SQL tables are bags not sets of rows with NULLs

User · Answer

While writing queries using inner joins the records will fetches from both tables if the condition satisfied on both tables  i e  exact match of the common column in both tables   While writing query using cross join the result is like cartesian product of the no of records in both tables  example if table1 contains 2 records and table2 contains 3 records then result of the query is 2 3   6 records   So dont go for cross join until you need that

User · Answer

SQL Server also accepts the simpler notation of   SELECT A F          B G          C H    FROM TABLE A A          TABLE B B          TABLE C C  WHERE A X   B X     AND B Y   C Y   Using this simpler notation  one does not need to bother about the difference between inner and cross joins  Instead of two  ON  clauses  there is a single  WHERE  clause that does the job  If you have any difficulty in figuring out which  JOIN   ON  clauses go where  abandon the  JOIN  notation and use the simpler one above   It is not cheating

User · Answer

It depends on the output you expect   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   The cross join will have 100 rows returned and they won t be related  just what is called a Cartesian product  The inner join will match records to each other  Assuming one has a primary key and that is a foreign key in the other you would get 10 rows returned   A cross join has limited general utility  but exists for completeness and describes the result of joining tables with no relations added to the query  You might use a cross join to make lists of combinations of words or something similar  An inner join on the other hand is the most common join

User · Answer

Inner Join  The join that displays only the rows that have a match in both the joined tables is known as inner join  This is default join in the query and view Designer   Syntax for Inner Join  SELECT t1 column name t2 column name FROM table name1 t1 INNER JOIN table name2 t2 ON t1 column name t2 column name   Cross Join  A cross join that produces Cartesian product of the tables that involved in the join  The size of a Cartesian product is the number of the rows in first table multiplied by the number of rows in the second table   Syntax for Cross Join  SELECT   FROM table name1 CROSS JOIN table name2   Or we can write it in another way also  SELECT   FROM table name1 table name2   Now check the query below for Cross join  Example  SELECT   FROM UserDetails CROSS JOIN OrderDetails   Or  SELECT   FROM UserDetails  OrderDetails

User · Answer

Cross join does not combine the rows  if you have 100 rows in each table with 1 to 1 match  you get 10 000 results  Innerjoin will only return 100 rows in the same situation   These 2 examples will return the same result   Cross join  select   from table1 cross join table2 where table1 id   table2 fk id   Inner join  select   from table1 join table2 on table1 id   table2 fk id   Use the last method

User · Answer

The inner join will give the result of matched records between two tables where as the cross join gives you the possible combinations between two tables

[sql] CROSS JOIN vs INNER JOIN in SQL

1. INNER JOIN

2. CROSS JOIN

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql

Examples related to cross-join