-6

What's the diference between CROSS JOIN and INNER JOIN? i.e.

table 1
+ID   +NAME + LAST__NAME
+-----+-----+-----------
+1    +John + Doe
+2    +Phil + Smith
+3    +Jane + Peterson


table2

+ID+Department
+--+----------
+1 + TI
+2 + Sales
+3 + TI

SELECT *
FROM table1
INNER JOIN table2
ON table1.id=table2.id;

SELECT *
FROM table1
CROSS JOIN table2

I want to know what's the diference between results

Constantin Groß
  • 10,719
  • 4
  • 24
  • 50
fsalazar_sch
  • 348
  • 2
  • 6
  • 17
  • cross join: all permutations returned, size=m*n. inner join: return size is a range of 0 to min(m or n). hope that helps :> – Drew Jul 17 '15 at 19:41
  • 1
    possible duplicate of [CROSS JOIN vs INNER JOIN in SQL Server 2008](http://stackoverflow.com/questions/17759687/cross-join-vs-inner-join-in-sql-server-2008) – shruti1810 Jul 17 '15 at 19:51
  • Another term that may come up is a cartesian product, which is what a cross join returns and is the result of all possible permutations, as Drew explained. – AdamMc331 Jul 17 '15 at 19:51

4 Answers4

7

An inner join (or just a join) allows you to apply a condition (the on clause) that specifies how the two tables should be joined. A cross join creates a Cartesian product - i.e., it matches every row from the first table with every row from the second table.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
4

https://en.wikipedia.org/wiki/Join_(SQL)#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.[1]

https://en.wikipedia.org/wiki/Join_(SQL)#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.

ceejayoz
  • 176,543
  • 40
  • 303
  • 368
4

Cross Join does not require (in fact you are not allowed) conditions An inner join requires a conditions (an On) clause

A Cross Joins behavior is identical to an inner join where the condition[s] do not filter anything out...

i.e., 

tableA Cross Join tableB

generates identical results to:

tableA Inner Join tableB on 1 = 1
Charles Bretana
  • 143,358
  • 22
  • 150
  • 216
0

Cross joins are rarely used, if they are, it just returns all the rows between the 2 joined tables. Inner join are more often used to join 2 or more tables with matching columns.

my779
  • 59
  • 6