MYSQL joins

The MYSQL joins are explained below with examples

Inner Join( Equi-Join)
In the inner-join the  columns of two tables. It can be used to select certain fields from both tables and only the correct rows will be joined together.

Syntax:

SELECT <column_name> FROM <Table1>, <Table2> WHERE (Table1.column = Table2.column)  

xample :

SQL query:

SELECT test1.id, test1.name, test2.id, test2.favcolor
FROM test1, test2
WHERE (
test1.name = test2.name

)

Id	name	id	favcolor
1	sree	1	black
2	divs	2	white

Cross- Join

Cross-join of two tables takes data of each row in table1 and joins it to the data from each row in table2.

Syntax: SELECT <column_names> FROM <table1>, <table2>

Example :

test1

Id	Name
1	Sree
2	divs
3	dev

test2

Id	favcolor	Name
1	black	Sree
2	white	divs

SQL query:

SELECT test1.id, test1.name, test2.id, test2.favcolor
FROM test1, test2

id	name	id	favcolor
1	sree	1	black
2	divs	1	black
3	dev	1	black
1	sree	2	white
2	divs	2	white
3	dev	2	white

Left Join &Right join

Left Join

according to the match condition.It will show all values from left table whether there is amatching value in right or not

Syntax:

SELECT <column_name> FROM <Table1> [join|LEFT JOIN |RIGHT JOIN ] <Table2> on  (Table1.column = Table2.column)  

for example consider the following two tables

testtable1

eid	eName	eBasicpay
1	sree	200000
2	divs	500000
3	vid	000000

testtable2

id	eid	phone
1	1	987654
2	3	322352362
3	5	1222222

example :

SQL query:

normal join will show a result like this

select eName , eBasicpay, phone

from testtable1     join testtable2   on testtable1.eid  =  testtable2.eid  

eName	eBasicpay	phone
sree	200000	987654
vid	1000000	322352362

The result of left join is    SELECT eName, eBasicpay, phone
FROM testtable1
LEFT JOIN testtable2 ON testtable1.eid = testtable2.eid  

eName	eBasicpay	phone
sree	200000	987654
divs	500000	NULL
vid	1000000	322352362

Right join

according to the match condition.It will show all values from right table whether there is amatching value in left or not

example :

SQL query:

SELECT eName, eBasicpay, phone
FROM testtable1
RIGHT JOIN testtable2 ON testtable1.eid = testtable2.eid     

eName	eBasicpay	phone
sree	200000	987654
vid	500000	322352362
NULL	NULL	1222222

You can use 'USING' clause on the Left /Right Join , if the columns that are carrying out the join on have the same name.

Syntax:

SELECT <column_name>  FROM <Table1>  LEFT JOIN <Table2>  USING (<column_name>) example :

SQL query:

SELECT *
FROM testtsable1
LEFT JOIN testtsable2 using(eid)

eid	eName	eBasicpay	id	eid	phone
1	Sree	200000	1	1	987654
2	div	500000	NULL	NULL	NULL
3	vid	500000	2	3	322352362

Joining three tables   for example take the following table as the third table   testtable3

id	eid	favcolor
1	1	black
2	2	blue
3	5	green

SQL Query
SELECT *
FROM testtsable1
LEFT JOIN testtsable2 ON testtsable1.eid = testtsable2.eid
LEFT JOIN testtsable3 ON testtsable1.eid = testtsable3.eid 

eid	eName	eBasicpay	id	eid	phone	id	eid	favcolor
1	Sree	200000	1	1	987654	1	1	black
2	div	500000	NULL	NULL	NULL	2	2	blue
3	vid	500000	2	3	322352362	NULL	NULL	NULL

Good Luck :)