SQL Join Query

create table tbl2 (Fname varchar(10),address varchar(15));
insert into tbl2 values ('AFname','Aaddress');
insert into tbl2 values ('BFname','Baddress');
insert into tbl2 values ('AFname','AAaddress');
insert into tbl2 values ('DFname','DAaddress');

JOIN
SELECT tbl1.Fname,tbl1.age,tbl2.address FROM tbl1,tbl2;
SELECT tbl1.Fname,tbl1.age,tbl2.address FROM tbl1,tbl2 WHERE tbl1.Fname=tbl2.Fname; SELECT t1.Fname,t1.age,t2.address FROM tbl1 AS t1,tbl2 AS t2;
SELECT t1.Fname,t1.age,t2.address FROM tbl1 AS t1,tbl2 AS t2 WHERE t1.Fname=t2.Fname;

INNER JOIN
SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield

The INNER JOIN returns all rows from both tables where there is a match.If there are rows in first table that do not have matches in second tables,those rows will not be listed.

SELECT tbl1.Fname,age,city,tbl2.address
FROM tbl1
INNER JOIN tbl2 ON tbl1.Fname=tbl2.Fname;
SELECT * FROM tbl1 INNER JOIN tbl2 ON tbl1.Fname=tbl2.Fname;

LEFT JOIN
SELECT field1, field2, field3
FROM first_table
LEFT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield

The LEFT JOIN returns all the rows from the first table , even if there are no matches in the second table.If there are rows in first table that do not have matches in second table,those rows also will be listed.

select * from tbl1;
select * from tbl2;
SELECT tbl1.Fname,age,city,tbl2.address
FROM tbl1
LEFT JOIN tbl2 ON tbl1.Fname=tbl2.Fname;
SELECT * FROM tbl1 LEFT JOIN tbl2 ON tbl1.Fname=tbl2.Fname;

RIGHT JOIN
SELECT field1, field2, field3
FROM first_table
RIGHT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield
select * from tbl1;
select * from tbl2;
SELECT tbl1.Fname,age,city,tbl2.address
FROM tbl1
RIGHT JOIN tbl2 ON tbl1.Fname=tbl2.Fname;
SELECT * FROM tbl1 RIGHT JOIN tbl2 ON tbl1.Fname=tbl2.Fname;
SELECT * FROM tbl1 RIGHT JOIN tbl2 ON tbl1.Fname=tbl2.Fname WHERE tbl1.city='Acity';