Cross join

In this lesson we are going to study about Corss joins in a database.
Cross Join  or Cartesian Product:

• Each row from the first table is combined with the each rows from the second table.
• Rows in the result table is the product of rows in each table.
• For large no of rows ,it takes longer time.
• It does not include any join condition .
• A Cross Join B Result table as  A*B.

Example1:In the below diagram we are having two table's Table:A,Table:B .Table:A is having one column X

with three data and Table:B is having one column Y with two data.So the value of  m=3 and n=2.
So when we are doing cross join of Table:A with Table:B this will return a table which have
m*n =3*2
       =6(Record)
i.e each row of one table is mapped with each row of second table.




Example2:  In the below diagram we are having two table's Table:R,Table:S .Table:A is having Two column A,column B

with three data and Table:S is having two column B.column C with two data.So the value of  m=3 and n=2.
So when we are doing cross join of Table:A with Table:B this will return a table which have
m*n =3*2
       =6(Record)
NOTE: Result table is having all columns of Table:R,Table:S

Syntax:
Explicit cross join:

SELECT *

FROM Table1
CROSS JOIN Table2



Implicit cross join:

SELECT *
FROM  Table1 , Table2;

just have a look on below video and try to Cross Join table by your self and you can download the sql scrpit used in this video from here DOWNLOAD LINK

 Related Article

• Join
• Equi Join
• Non  Equi Join
• Self Join 
• Natural Join  
• Join with Using clause.
• Join with ON clause
• Outer Join.  

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Joins

In this lesson we are going to study about joins in a database . In order to join rows from multiple table or view we use join in our statement in this session we will briefly discussing the join and type of join in database .
JOINS:

• Joins query are written in the where clause of the select statement.
• Joins condition :comparing two columns of two tables.
• Database engine joins the table according to the join condition .
• If we want to join more then two table then database engine evaluates columns of the two tables and then it joins the result to the other table.

Have a look this image which shows the operation of the join.

Table A and Table B is having some common columns .by giving the condition in where clause we can have the result of joined table.

NOTE: The columns which are using in join condition must have the same datatype . otherwise it will give error and to prevent this cause if having different datatype but still we want to use that columns in join condition then use the cast function to convert the datatype and then use in join condition .

Type of Joins:

 Joins are classified according to the different type way the join operation performed on the tables.

we will discuss about all this Join in details in the next section .
Related Article

• Cross Join 
• Equi Join
• Non  Equi Join
• Self Join 
• Natural Join 
• Join with Using clause.
• Join with ON clause
• Outer Join.  

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends: