Introduction
Joins are one of the most important operations performed by a relational database system. An RDBMS uses joins to match rows from one table with rows from another table. We can join two tables explicitly by writing a query that lists both tables in the FROM clause. We can also join two tables by using a variety of different sub queries. Finally SQL Server may introduce joins for a variety of purposes into a query plan during optimization. The SQL JOIN clause is used to retrieve data from 2 or more tables joined by common fields. The most common scenario is a primary key from one of the tables matches a foreign key in second table. The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.
Type of join
- Inner join
- Outer join
- Cross join
- Cross apply
- Semi-join
- Anti-semi-join
INNERSpecifies all matching pairs of rows are returned. Discards unmatched rows from both tables. This is the default if no join type is specified.
LEFT [OUTER]Specifies that all rows from the left table not meeting the specified condition are included in the result set, and output columns from the right table are set to NULL in addition to all rows returned by the inner join.
RIGHT [OUTER]Specifies that all rows from the right table not meeting the specified condition are included in the result set, and output columns from the left table are set to NULL in addition to all rows returned by the inner join.
FULL [OUTER]If a row from either the left or right table does not match the selection criteria, specifies the row be included in the result set, and output columns that correspond to the other table be set to NULL. This is in addition to all rows usually returned by the inner join.Example
DELETE FROM DVDs
USING DVDs, Studios
WHERE DVDs.StudID=Studios.StudID
AND Studios.StudDescrip='Universal Studios