Hello Everyone,
Today i was working to prepare some notes for my team with respect to Database and i wanted to share the same with you all. The notes is about usages of join in the query.
The result is cartessian product of two tables, i.e, Table A* Table B number of rows.
select * from company cross join employee
select * from company,employee
INNER JOIN OR EQUI JOIN :-
This is the type of join where tables are combined based on a common column.
select * from company,employee where company.id=employee.companyid
select * from company inner join employee on company.id=employee.companyid
OUTER JOIN:-
Join is used to combine all rows of one table with matching rows from the other table and also show unmatchable records from other table. It is used whenever multiple tables must be accessed through a SQL SELECT statement
Left Outer Join:-
The below Query return you the set of records or rows from table compnay matches with table employee. The table also display the set of records from left table that do not matches with the right table. The unmatchable records are displayed with null values.
select * from company left join employee on company.id=emlpoyee.companyid
Right Outer Join:-
This join return rows that have matching data in the right table
select * from company right join employee on company.id=employee.companyid
FULL JOIN :-
The Full Join return you the combine result set of both table and return a null value for missing matches on both side of the table. For example, this will helps you to see each records from company who is in employee,but also help you to see each records from company which is not a part of a employee. and each employee which does not have an company. The UNION clause returns the results of two SQL queries into a single result set table..
select * from company left join employee on company.id=employee.companyid union select * from company right join employee on company.id=employee.companyid
NATURAL JOIN :-
Natural Join is a specialization of equi-joins. The join compares all columns in both tables that have the same column-name in both tables that have column name in the joined table. The resulting set include only one column for each pair of the same named column
select * from company natural left join employee
Today i was working to prepare some notes for my team with respect to Database and i wanted to share the same with you all. The notes is about usages of join in the query.
CROSS JOIN :-
The result is cartessian product of two tables, i.e, Table A* Table B number of rows.
select * from company cross join employee
select * from company,employee
INNER JOIN OR EQUI JOIN :-
This is the type of join where tables are combined based on a common column.
select * from company,employee where company.id=employee.companyid
select * from company inner join employee on company.id=employee.companyid
OUTER JOIN:-
Join is used to combine all rows of one table with matching rows from the other table and also show unmatchable records from other table. It is used whenever multiple tables must be accessed through a SQL SELECT statement
Left Outer Join:-
The below Query return you the set of records or rows from table compnay matches with table employee. The table also display the set of records from left table that do not matches with the right table. The unmatchable records are displayed with null values.
select * from company left join employee on company.id=emlpoyee.companyid
Right Outer Join:-
This join return rows that have matching data in the right table
select * from company right join employee on company.id=employee.companyid
FULL JOIN :-
The Full Join return you the combine result set of both table and return a null value for missing matches on both side of the table. For example, this will helps you to see each records from company who is in employee,but also help you to see each records from company which is not a part of a employee. and each employee which does not have an company. The UNION clause returns the results of two SQL queries into a single result set table..
select * from company left join employee on company.id=employee.companyid union select * from company right join employee on company.id=employee.companyid
NATURAL JOIN :-
Natural Join is a specialization of equi-joins. The join compares all columns in both tables that have the same column-name in both tables that have column name in the joined table. The resulting set include only one column for each pair of the same named column
select * from company natural left join employee
No comments:
Post a Comment