各种join

DBMS好久没有看了,一些基本感念都忘记了,Four types of JOINs: INNEROUTERLEFT, and RIGHT

  • inner join,最default的join!Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate.
  • cross join: The cross join does not apply any predicate to filter records from the joined table. Programmers can further filter the results of a cross join by using a WHERE clause.
  • left outer join, 保留左表中所有数据,如果在右表中没有match的item,则null!The result of a left outer join (or simply left join) for table A and B always contains all records of the “left” table (A), even if the join-condition does not find any matching record in the “right” table (B).
  • right outer join,同上,只是保留右表数据,若左边没有match tuple,null填充!
  • self join: EXAMPLE -> find all pairings of two employees in the same country. All the employee information is contained within a single large table.  USE self join:
SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F, Employee S
WHERE F.Country = S.Country
AND F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s