Join
By using joins, we can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how SQL Server should use data from one table to select the rows in another table. Sql server joins are used to combine result data from two or more tables.
There are three types of sql server joins.
1. Inner Join
2. Outer Join
3. Cross Joins
1. Inner Join
Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. Inner joins return rows only when there is at least one row from both tables that matches the join condition. Eg:Retrieving all rows where the student identification number is the same in both the students and courses tables.
2. Outer join
Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause.
There are there 3 types of outer joins.
a. Left outer join
All rows are retrieved from the left table referenced with a left outer join. If matching records are found then it will display data of right table with left table data otherwise put a null values instead of right table data.
b.Right outer join
All rows are retrieved from the right table referenced in a right outer join. If matching records are found then it will display data of left table with right table data otherwise put a null value instead of left table data.
c.Full outer join
All rows from both tables are returned in a full outer join.This joins are combination of both left outer join and right outer join.
3. Cross join
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.
Self-join
A table can be joined to itself in a self-join.
By using joins, we can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how SQL Server should use data from one table to select the rows in another table. Sql server joins are used to combine result data from two or more tables.
There are three types of sql server joins.
1. Inner Join
2. Outer Join
3. Cross Joins
1. Inner Join
Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. Inner joins return rows only when there is at least one row from both tables that matches the join condition. Eg:Retrieving all rows where the student identification number is the same in both the students and courses tables.
2. Outer join
Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause.
There are there 3 types of outer joins.
a. Left outer join
All rows are retrieved from the left table referenced with a left outer join. If matching records are found then it will display data of right table with left table data otherwise put a null values instead of right table data.
b.Right outer join
All rows are retrieved from the right table referenced in a right outer join. If matching records are found then it will display data of left table with right table data otherwise put a null value instead of left table data.
c.Full outer join
All rows from both tables are returned in a full outer join.This joins are combination of both left outer join and right outer join.
3. Cross join
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.
Self-join
A table can be joined to itself in a self-join.
No comments:
Post a Comment