join in SQL
In a relational database, data is distributed in multiple logical tables. To get a complete meaningful set of data, you need to query data from these tables using joins. SQL Server supports many kinds of joins.
The join clause allows us to retrieve data from two or more related tables, based on a related column between them into a meaningful result set.
The SQL Server Joins are classified into two types such as
ANSI format JOINS
NON-ANSI format JOINS
ANSI format JOINS
Further, the outer join is divided into three types are as follows
Inner join
Outer join
Left outer join
Right outer join
Full outer join
Cross join
NON-ANSI format JOINS
EQUI join
NON-EQUI join
SELF-join
Natural Join
Inner Join in SQL Server
The Inner Join in SQL Server is used to return only the matching rows from both the tables involved in the join by removing the non-matching records
We did not get those records that have the NULL value in the EmployeeId column of the Projects table.
Instead of using the INNER JOIN keyword, we can also use the JOIN keyword
Outer Join in SQL Server
A. Left outer join
The OUTER JOIN in SQL Server returns matched data rows as well as unmatched data rows from both the tables involved in the join.
The LEFT OUTER JOIN in SQL Server is used to retrieve all the matching rows from both the tables involved in the join as well as non-matching rows from the left side table. In this case, the un-matching data will take a null value.
you can use either the LEFT OUTER JOIN or LEFT JOIN keyword to perform Left Outer Join
B. Right outer join
The RIGHT OUTER JOIN in SQL Server is used to retrieve all the matching rows from both the tables involved in the join as well as non-matching rows from the right-side table. In this case, the un-matching data will take NULL values.
Instead of using the RIGHT OUTER JOIN keyword, we can also use the RIGHT JOIN keyword.
C. Full Outer Join in SQL Server
The Full Outer Join in SQL Server is used to retrieve all the matching records as well as all the non-matching records from both the tables involved in the JOIN. The Un-matching data in such cases will take the NULL values.
you can use either the FULL OUTER JOIN or FULL JOIN keyword to perform Full Outer Join
Cross Join in SQL Server
The CROSS JOIN is created by using the CROSS JOIN keyword. The CROSS JOIN does not contain an ON clause. In Cross Join, each record of a table is joined with each record of the other table.
A Cross Join in SQL Server produces the Cartesian product of the tables involved in the join. The Cartesian product means the number of records present in the first table is multiplied by the number of records present in the second table.
If The Employee is the LEFT Table which contains 10 rows and Projects is the RIGHT Table which contains 11 rows. So, when you execute the above query, you will get 110 records in the result set.
EQUI JOIN in Sql Server
EQUI JOIN creates a JOIN for equality or matching column(s) values of the relative tables.
EQUI JOIN also create JOIN by using JOIN with ON and then providing the names of the columns with their relative tables to check equality using equal sign (=).
NON-EQUI Join in Sql Server
NON EQUI JOIN performs a JOIN using comparison operator other than equal(=) sign like >, <, >=, <= with conditions.
Self Join Join in Sql Server
A self join is a regular join, but the table is joined with itself.
To use a self join, the table must contain a column (call it X) that acts as the primary key and a different column (call it Y) that stores values that can be matched up with the values in Column X.
The values of Columns X and Y do not have to be the same for any given row, and the value in Column Y may even be null.
Natural Join in Sql Server
The SQL natural join is a type of equi-join that implicitly combines tables based on columns with the same name and type.
To perform natural join there must be one common attribute(Column) between two tables. Natural join will retrieve from multiple relations. It works in three steps.
The result set contains only one column for each pair of equally named columns. If no columns with the same names are found, the result will be a cross join.
The Natural Joins are not supported in the SQL Server. It works only in oracle and mysql
The NATURAL keyword places an implicit USING clause to the join constraints.
It forms a USING list consisting of all column names that appear in both input tables.
