SQL Primary/Foreign Key
Primary Key
A table usually has a column or combination of columns whose values uniquely identify each row in the table -- this column (or columns) is called the primary key of the table
INNER JOIN, Example
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName
LEFT JOIN, Example
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName
FULL JOIN
SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name
INNER JOIN
SELECT column_name(s) FROM table_name2 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name
UNION
SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2
A primary key is a value
that must be unique within the table; it binds data together, across tables, without repeating all of the data in every table
Tables in a database
are often related to each other with keys
The primary key of the table
enforces the entity integrity of the table
Foreign Key (FK)
is a column or combination of columns used to establish and enforce a link between the data in two tables A link is created between two tables by adding the column or columns that hold one table's primary key values to the other table-- this column becomes a FK in the 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.
The UNION operator:
is used to combine the result-set of two or more SELECT statements
The FULL JOIN keyword:
return rows when there is a match in one of the tables
The LEFT JOIN keyword:
returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2)
The RIGHT JOIN keyword:
returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1)
The INNER JOIN keyword:
returns rows when there is at least one match in both tables