A structured query language (SQL) operation created on two or more database tables. Complex queries in SQL usually include join commands. Some typical joins:
- Inner Join: (default) Returns all rows from two tables where the key matches in BOTH tables. A record that does not contain a matching value is excluded from the returned output.
- Left Outer Join: Returns all rows from the left table with the matching rows in the right table. If there is no match in the right table, it sets the missing values to NULL in the results table.
- Right Outer Join: Returns all rows from the right table with the matching rows in the left table. If there is no match in the left table, it sets the missing values to NULL in the results table.
- Full Outer Join: Returns all the rows from both left and right tables. All rows will be returned in the results table, regardless of whether they are matched in the other table or not.
- Self-join: A table can be joined to itself in a self-join. In this case, the table is joined to a copy of itself. Field names for the table are specified as aliases to indicate which table’s fields are referenced in the query.
- Cross join (or Cartesian product): The simplest type of join. A matching column is not specified with a WHERE clause, so the result set contains every row of the first table as well as each row in the second table. So if table1 has 2 rows and table2 has 3, the output set will have 6 rows.