tailieunhanh - Hướng dẫn học Microsoft SQL Server 2008 part 29

Một trái bên ngoài tham gia sẽ mở rộng bên trong tham gia và bao gồm các hàng từ bảng Một không có một trận đấu: Thing1 SELECT, Thing2 TỪ | Part II Manipulating Data With Select INSERT OnePK Thingl VALUES 2 New Thing INSERT OnePK Thingl VALUES 3 Red Thing INSERT OnePK Thingl VALUES 4 Blue Thing INSERT TwoPK OnePK Thing2 VALUES 1 0 Plane INSERT TwoPK OnePK Thing2 VALUES 2 2 Train INSERT TwoPK OnePK Thing2 VALUES 3 3 Car INSERT TwoPK OnePK Thing2 VALUES 4 NULL Cycle FIGURE 10-9 The Red Thing Blue Thing example has data to view every type of join. Old Thing Blue Thing Plane Cycle An inner join between table One and table Two will return only the two matching rows SELECT Thingl Thing2 FROM INNER JOIN ON Result Thingl Thing2 New Thing Train Red Thing Car A left outer join will extend the inner join and include the rows from table One without a match SELECT Thingl Thing2 FROM LEFT OUTER JOIN ON 242 Merging Data with Joins and Unions 10 All the rows are now returned from table One but two rows are still missing from table Two Thing1 Thing2 Old Thing NULL New Thing Train Red Thing Car Blue Thing NULL A full outer join will retrieve every row from both tables regardless of a match between the tables SELECT Thing1 Thing2 FROM FULL OUTER JOIN ON The plane and cycle from table Two are now listed along with every row from table One Thing1 Thing2 Old Thing NULL New Thing Train Red Thing Car Blue Thing NULL NULL Plane NULL Cycle As this example shows full outer joins are an excellent tool for finding all the data even bad data. Set difference queries explored later in this chapter build on outer joins to zero in on bad data. Placing the conditions within outer joins When working with inner joins a condition has the same effect whether it s in the JOIN clause or the WHERE clause but that s not the case with outer joins When the condition is in the JOIN clause SQL Server includes all rows from the outer table and then uses the condition