Ans: An SQL join clause combines columns from one or more tables in a relational database. It creates a set that can be saved as a table or used as it is. A JOIN is a means for combining columns from one (self-join) or more tables by using values common to each.
Ans: The list of different types of SQL JOINs as follows:
Ans: SQL LEFT JOIN Keyword. The LEFT JOIN keyword returns all records from the lefttable (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
Ans: SQL FULL OUTER JOIN Keyword. The FULL OUTER JOIN keyword return all records when there is a match in either left (table1) or right (table2) table records.
Note: FULL OUTER JOIN can potentially return very large result-sets!
Ans: Using Self-Joins. A table can be joined to itself in a self-join. Use a self-join when you want to create a result set that joins records in a table with other records in the same table. To list a table two times in the same query, you must provide a table alias for at least one of instance of the table name
Ans: Right join is used to retrieve rows which are common between the tables and all rows of Right hand side table. It returns all the rows from the right hand side table even though there are no matches in the left hand side table
Ans: Left join is used to retrieve rows which are common between the tables and all rows of Left hand side table. It returns all the rows from Left hand side table even though there are no matches in the Right hand side table.
Ans: Full join return rows when there are matching rows in any one of the tables. This means, it returns all the rows from the left hand side table and all the rows from the right hand side table.
Ans: Both inner join and outer join are the joining technique where matching records from the participating tables are displayed. That is when two tables are joined, based on the common column in both the tables, records are pulled.
In inner join method, when two tables are joined, only those records from both the tables are retrieved for which there exact match is based on the common column. This common column is based on the primary key of one table and is foreign key in other table. When there is exact match for these two column values records are retrieved.
Ans: A self-join is simply a normal SQL join that joins one table to itself. Joining a table to itself can be useful when you want to compare values in a column to other values in the same column.
Ans: A self-join can be an inner join or an outer join or even a cross join. A table is joined to itself based upon a column that have duplicate data in different rows.
LEFT JOIN and RIGHT JOIN?Ans:LEFT JOIN and RIGHT JOIN actually both do very similar things: they display the results of a JOIN query including all records on a given table. The only difference is that LEFT JOIN displays all records on the left table of the query, and RIGHT JOIN displays all records on the right table!