January 16, 2009 at 2:55 am
Can anybody tell me the Difference between inner join and cross join.
how and when we have to use them?
January 16, 2009 at 3:09 am
Dude...I would suggest you to go through the BOL and then come up with your doubts
-Vikas Bindra
January 16, 2009 at 3:10 am
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]
January 16, 2009 at 3:14 am
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
January 16, 2009 at 3:26 am
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?
January 16, 2009 at 4:00 am
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
January 16, 2009 at 6:24 am
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,
January 16, 2009 at 7:17 am
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
April 11, 2022 at 8:59 pm
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
April 12, 2022 at 1:14 am
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
Change is inevitable... Change for the better is not.
April 12, 2022 at 7:57 am
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/
April 12, 2022 at 10:19 am
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.
April 12, 2022 at 5:07 pm
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
Change is inevitable... Change for the better is not.
August 29, 2022 at 9:39 am
This reply has been reported for inappropriate content.
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
To Learn More about Web development Course Visit https://www.a2nacademy.com/course/web-design-course
March 21, 2023 at 6:19 am
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply