Advanced SQL Flashcards
A(n) ___ ___ clause is used to combine records from two or more tables in a database
based on a common field between them
SQL JOIN
return rows when there is at least one match in both tables.
INNER Join
an extension of INNER JOIN, does not require records in the two joined tables to have a matching record
OUTER Join
OUTER Join subdivision
- left outer joins
- right outer joins
- full outer joins
returns all rows from the left table, even if there are no matches in the right table
LEFT JOIN
returns all the rows from the right table, even if there
are no matches in the left table
RIGHT JOIN
if rows are being matched in table A and table B, then all rows from table A are displayed even if there is no matching row in table B, and vice versa
FULL JOIN
returns all possible combinations of rows from the two tables.
CROSS Join
SELECT statement embedded within another SELECT statement.
Subqueries
Types of Subqueries
returns a single column and a single row, that is, a single value
Scalar subquery
Types of Subqueries
returns multiple columns, but only a single row
Row subquery
Types of Subqueries
returns one or more columns and multiple rows.
Table subquery
used to improve the efficiency of searches and to avoid duplicate column values.
SQL Indexes
used to delete index.
Drop Index
a virtual table based on a SELECT query
view
