fourthly day

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 columns

FROM table1 JOIN

(table2 JOIN table3

ON 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.