JOins
Jions a common way to combine table in SQL.
1
we use the JOIN keyword and an ON clause.
syntax is
SELECT columns
FROM table1 JOIN table2
ON table1.column1=table2.column1
Note that if you want to jion the two table, the two table must share at least one common column.
the other way to join the two table
the syntax is
SELECT *
FROM Course c, Prereq p
WHERE c.course_number= p.course_number
in the secord way you will not find the ON and JOIN keyword, but you use the WHERE key word.
2
the Cartesian product
the syntax is
SELECT *
FROM Course c, prereq p
the other way is
SELECT *
FROM table1 CROSS JOIN Table2
why we need the cartesian product, in some suitation we need it?????
3
we often use the Equi-Joins ,but some we need Non-Equi-joins.
Suppose that we want to find all the students who are more senior than other students.
SELECT 'SENIORITY' = x.sname + ' is in a higher class than ' + y.sname
FROM Student AS x, Student AS y
WHERE y.class = 3--???
AND x.class > y.class
4
As with other SELECT statements, the ORDER BY clause can be used in joins to order the result set.
for example
SELECT c.course_name, c.course_number, c.credit_hours, c.offering_dept, p.prereq
FROM Course c JOIN Prereq p
ON c.course_number=p.course_number
ORDER BY c.course_number--结果以课程的数字排列顺序
或则将 c.course_number 改为它所在列的列号
5 Joining More Than Two Tables
we will frequently need to perform a join in which you have to get data
from more than two tables. A join is a pair-wise, binary operation.
In SQL Server, you can join more than two tables in either of two ways:
by using a nested JOIN, or by using a WHERE clause. Joins are always
done pair-wise.
the simplest form of the nested JOIN is as follows;
SELECT columnsFROM table1 JOIN(table2 JOIN table3ON table3.column3=table2.column2)ON table1.column1=table2.column2
6 the LEET OUTER JOIN and the RIGHT OUTER JOIN
sometimes when we join the two table,but the row of table is diffient, how to deal
with??
LEFT OUTER JOINs include all the rows from the first (left) of the two tables,
even if there are no matching values for the rows in the second (right) table.
RIGHT OUTER JOINs include all the rows from the second (right) of the two tables,
even if there are no matching values for the rows in the first (left) table.