ChatGPT解决这个技术问题 Extra ChatGPT

CROSS JOIN vs INNER JOIN in SQL

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?

CROSS JOIN will result in all possible combinations of the tables. e.g. Table1 with 100 rows and Table2 with 100 rows will result in 10000 records.
x CROSS JOIN y is x INNER JOIN y ON 1=1

C
Cœur

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

https://i.stack.imgur.com/fH3fU.jpg

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.

https://i.stack.imgur.com/2OqzE.jpg

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

Figure 2's key is complex: It encircles (colour irrelevant) elements of an argument of CROSS JOIN & a number (value irrelevant) is a row of it & a line (colour irrelevant) is a result row. For tables as bags it's not a Venn diagram: for rows as values, it's wrong; for rows as elements they can't be shared. For tables as sets you don't need a Venn diagram. Figure 1 is a common terrible attempt to explain JOIN. Its key is also complex: It's only for tables as sets & only equijoin & only one value; it also represents the input differently than the output. Write it for JOIN in general.
Figure 1 is useful and correct as the first of 4 colourings of intersecting circle Venn diagrams for: (INNER) JOIN vs LEFT, RIGHT & FULL (OUTER) JOIN but not vs CROSS JOIN. Intersection rows are in JOIN, left/right rows are extra (null-extended) rows in LEFT/RIGHT JOIN. It includes CROSS JOIN as a special case of (INNER) JOIN where there are no rows in the non-intersection.
Thanks for your suggestion. Anyhow, the OP has asked for the difference between these two joins. I have answered for this question in such a way that any beginner can easily understand the difference between them. As you said, I haven't given keys as in a Production environment. Its just an example to understand easily. And for Cross Join, do you think it doesn't return all the rows unless a Where clause is given? From your comments, there are more chances for a beginner to be get confused!!! @philipxy
My comments point out that the diagrams are hard to interpret even if one knows what they are trying to say and are inappropriate for this topic. By "key" (of Figure 2 or 1) I meant "explanation of what the parts of a diagram mean". By "write it" I mean try for yourself to write very clearly what the parts of the diagram mean. You will find that the diagrams are complicated and don't demonstrate cross join vs inner join! Ie they don't belong in your answer. PS Table relational keys have no role in explaining what JOINs do. PPS The only difference between the joins is INNER JOIN has an ON.
Look in W3Schools w3schools.com/sql/sql_join_inner.asp where they have given similar type of diagram for INNER JOIN. Make sure you are right before you make comments @philipxy
t
t-clausen.dk

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


And I believe you can write select * from table1 cross join table2 where table1.id = table2.fk_id as select * from table1, table2 where table1.id = table2.fk_id (replacing cross join with a ,)
@Lucas that is the old syntax for joins, but it will work. I recommend clausen's version though, much more readable.
what do you mean 1 to 1 match? What do you mean "same situation"
@iliketocode "old syntax for joins" is not clear, you'd better talk about SQL-89 and SQL-92 standards
Your INNER JOIN "100" is a special case that you have not explained & you have not explained the general case.
p
philipxy

CROSS JOIN = (INNER) JOIN = comma (",")

TL;DR The only difference between SQL CROSS JOIN, (INNER) JOIN and comma (",") (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 "Cartesian" 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 via product (7.5 1.b.ii), aka CROSS JOIN via (7.7 1.a) and (INNER) JOIN ON via 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 "implicit join notation" 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 & 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 >= M.[Minimum Age] AND C.Age = 18 */ FROM Customers C INNER JOIN Movies M ON C.CustomerID = M.CustomerID AND C.Age >= 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 "cross join"

Unfortunately the term "cross join" 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 "Don't use comma" 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 & 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 & 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 & 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.


"intermediate conceptual SQL-style Cartesian cross product" -- is that another way of saying, "it's not really a set of ordered pairs but I can't avoid saying 'Cartesian product'"? :) As a non-mathematician myself, the only context in which I have ever encountered the term 'Cartesian product' is when someone is explaining SQL's CROSS JOIN. I do wonder how often someone comes to SQL already familiar with Cartesian product but unable to figure out CROSS JOIN.
@onedaywhen Operator Cartesian product returns a set of ordered tuples given some sets. The result is a Cartesian product. Relational & SQL cultures sadly misuse/overload "Cartesian product" for operators that evoke but are not Cartesian product. Eg Wikipedia! I oppose that--it just misleads/confuses. Yet here unfortunately I only fuzzily describe how SQL cross join works in my own words and defer to Wikipedia. I label the result "intermediate conceptual SQL-style Cartesian cross product". Yes "Cartesian" is for those sadly already corrupted who use/expect it.
m
modulitos

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

V
Vlad Mihalcea

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:

https://i.stack.imgur.com/CnotY.png

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:

https://i.stack.imgur.com/PT3ov.png

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.


"INNER JOIN does not return the Cartesian Product of the two joining data sets" is a bit much when x INNER JOIN y on 1=1 does return it. So is "Instead" when what you go on to try to say does in fact (of course) return it for condition 1=1. Except that the language in the bullets does not clearly describe the result of an inner join. What does describe it is that it's a cross join less rows that don't meet the condition. Similarly, your cross join language using "combinations" is not clear.
@Vlad Mihalcea inner join is indeed just a cross join + where clause . You have to test matches by computing all row combinations, meaning you're just computing the cartesian product step by step while throwing away mismatching row pairs.
u
user3095896

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


d
drneel

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.


J
Jagadeesh G

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.


Only if WHERE clause is missing !
J
Javier Bañez

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.


s
sindhu

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.


S
Shrikant Jadhav

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.