What is a JOIN in SQL?
A JOIN clause is used to combine rows from two or more tables, based on a related column between these tables. The purpose of JOINS in SQL is to access data from various tables based on logical relationships. We can use joins to fetch matched or mismatched records from more than one table.
How many different forms of JOINs are there?
SQL Server supports different kinds of joins named as INNER JOIN, OUTER JOIN, SELF JOIN, and CROSS JOIN. Each of these joins types defines the method by which two tables are related in a query. OUTER JOINS can further be divided into LEFT OUTER JOINS, RIGHT OUTER JOINS, and FULL OUTER JOINS.
What is an INNER JOIN?
INNER JOIN: Retrieves a result set by merging rows from two or more tables with matching values. The INNER keyword is optional to use.
What is a LEFT OUTER JOIN?
LEFT OUTER JOIN: Retrieves a result set displaying all rows from the left table (first table). The left table (first table) is the table that is called before the Join clause, the main table that is being SELECTED, and then the matched records from the right table (the tables joined). The right table (joined table) is the table that is called after the Join clause that is being joined with the preceding table(s). The OUTER keyword is optional.
What is a RIGHT OUTER JOIN?
RIGHT OUTER JOIN: Retrieves a result set displaying all rows from the right table and the matched records from the left table (first table). Again, the OUTER keyword is optional.
What is a FULL OUTER JOIN?
FULL OUTER JOIN: Retrieves a result set of all the matching rows from both the left & right table.
What is a SELF JOIN?
SELF JOIN: Retrieves a result set by joining the table to itself, linking rows within the same table.
What is a CROSS JOIN?
CROSS JOIN: Retrieves a result set combining each row of the first table with each row of the second table displaying a set of rows where each record of one table is matched with each record from the other table.
What is a MERGE JOIN?
The most efficient join operator is the merge join. However, it necessitates that the join columns be used to order all input data. All the logical join operations such as inner join, left, right, and full outer join are supported by the merge join operator.
Even while merge join is a highly quick operation by itself, sort operations may make it a more expensive option. Merge join is frequently the fastest join technique on the market, though, provided the data volume is big and the needed data can be retrieved presorted from already-existing B-tree indexes. It is not necessary to enforce the ideal join type because the query optimizer typically chooses the optimum execution plan for a given select statement, although it occasionally can be helpful. Using the OPTION clause, the user can make sure the desired join type is used.
Big Data and SQL 