Inner and Outer Joins
Give this one a try later!
Matching left and right table rows always appear in a join. In some cases,
the database user may also want to see unmatched rows from left, right, or
both tables. To enable all cases, relational databases have four types of
joins:
An inner join selects only matching left and right table rows.
A left join selects all left table rows, but only matching right table rows.
A right join selects all right table rows, but only matching left table rows.
A full join selects all left and right table rows, regardless of match.
, Full joins are written with the FULL JOIN keywords. Unmatched rows from
both tables appear in the result.
Inner, left, right, and full joins are written with keywords INNER JOIN, LEFT
JOIN, RIGHT JOIN, and FULL JOIN. An ON clause specifies the join columns.
An outer join is any join that selects unmatched rows, including left, right,
and full joins.
FULL JOIN
Give this one a try later!
A type of SQL join where the resulting join output contains all rows from
both tables, in addition to the rows where the data in two matching
columns are equal.
Simple Primary Key
Give this one a try later!
A simple primary key consists of a single column
Left table vs Right table
Give this one a try later!
, The left table of a join is the first table in the FROM clause. The right table
of a join is the second table in the FROM clause. In the example above,
Department is the left table and Employee is the right table.
In the Department table, which foreign key value violates referential integrity?
Give this one a try later!
Since no employee has ID 3829, 3829 violates referential integrity.
Referential Integrity
Give this one a try later!
requires that all foreign key values must either be fully NULL or match some
primary key value.
foreign keys must obey referential integrity at all times. Occasionally, data
entry errors or incomplete data result in referential integrity violations.
Violations must be corrected before data is stored in the database.
Primary keys obey two rules
Give this one a try later!
Give this one a try later!
Matching left and right table rows always appear in a join. In some cases,
the database user may also want to see unmatched rows from left, right, or
both tables. To enable all cases, relational databases have four types of
joins:
An inner join selects only matching left and right table rows.
A left join selects all left table rows, but only matching right table rows.
A right join selects all right table rows, but only matching left table rows.
A full join selects all left and right table rows, regardless of match.
, Full joins are written with the FULL JOIN keywords. Unmatched rows from
both tables appear in the result.
Inner, left, right, and full joins are written with keywords INNER JOIN, LEFT
JOIN, RIGHT JOIN, and FULL JOIN. An ON clause specifies the join columns.
An outer join is any join that selects unmatched rows, including left, right,
and full joins.
FULL JOIN
Give this one a try later!
A type of SQL join where the resulting join output contains all rows from
both tables, in addition to the rows where the data in two matching
columns are equal.
Simple Primary Key
Give this one a try later!
A simple primary key consists of a single column
Left table vs Right table
Give this one a try later!
, The left table of a join is the first table in the FROM clause. The right table
of a join is the second table in the FROM clause. In the example above,
Department is the left table and Employee is the right table.
In the Department table, which foreign key value violates referential integrity?
Give this one a try later!
Since no employee has ID 3829, 3829 violates referential integrity.
Referential Integrity
Give this one a try later!
requires that all foreign key values must either be fully NULL or match some
primary key value.
foreign keys must obey referential integrity at all times. Occasionally, data
entry errors or incomplete data result in referential integrity violations.
Violations must be corrected before data is stored in the database.
Primary keys obey two rules
Give this one a try later!
