tailieunhanh - SQL Anywhere Studio 9- P3

Tham khảo tài liệu 'sql anywhere studio 9- p3', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | 86 Chapter 3 Selecting SELECT FROM child LEFT OUTER JOIN parent ON ORDER BY Tip Outer joins are confusing at the best of times so don t make the situation worse by using both LEFT OUTER JOIN and RIGHT OUTER JOIN operators. Stick with LEFT OUTER JOIN and your code will be easier to understand because the preserved table will always be on the same side. FULL OUTER JOIN The FULL OUTER JOIN operator is an extension that combines both LEFT OUTER JOIN and RIGHT OUTER JOIN functionality. In other words all the rows in both tables are preserved and both tables are null-supplying when they have to be. Here s how it works First the INNER JOIN is computed using the ON condition. Second any rows from the left-hand table that weren t included by the INNER JOIN process are now appended to the result set with NULL values used for the columns that would normally come from the right-hand table. And finally any rows from the right-hand table that weren t included by the INNER JOIN process are now appended to the result set with NULL values used for the columns that would normally come from the left-hand table. Here s what the FULL OUTER JOIN looks like using the parent and child tables SELECT FROM parent FULL OUTER JOIN child ON ORDER BY Now the result set contains all the columns from all the rows in both tables. It includes parent-and-child combinations from the INNER JOIN plus the orphan child row from the RIGHT OUTER JOIN plus the childless parent rows from the LEFT OUTER JOIN. parent. parent_key parent. data_1 child. child_key child. parent_key NULL NULL 7 NULL -- orphan 1 x 4 1 -- parent and child 1 x 5 1 -- parent and child 1 x 6 1 -- parent and child 2 x NULL NULL -- parent with no children 3 y NULL NULL .