Đang chuẩn bị liên kết để tải về tài liệu:
The Language of SQL- P30

The Language of SQL- P30:Research has shown that, being pressed for time, most readers tend to skip the introduction of any book they happen to read and then proceed immediately to the first real chapter. With that fact firmly in mind, we will only cover relatively unimportant material in the introduction, such as an explanation of what you will and will not learn by reading this book. | CHAPTER 13 Self Joins and Views Keywords Introduced CREATE VIEW ALTER VIEW DROP VIEW The inner and outer joins of the past two chapters have dealt with various ways of combining data from multiple tables. We re now going to examine alternate ways of using and defining tables. Previously we ve always assumed that the data we re looking at physically exists in tables in a database. We ll now turn to two techniques that will let us view data in a virtual way. The first technique the self join allows you to refer to the same table twice as if it were two separate tables. In essence the self join creates a virtual view of a table allowing it to be used more than once. Second you ll learn about database views which are a useful concept that enables you to create new virtual tables at will. Self Joins The self join lets you join a table to itself. The most common use of the self join is to deal with tables that are self-referencing in nature. These are tables that have a column that refers to another column in the same table. A common example of this type of relationship is a table that contains information about employees. In this example each row in a Personnel table has a column that points to another row in the same table representing the employee s manager. In some ways this is similar to the concept of foreign keys. The main difference is that whereas foreign keys point to columns in other tables we now have columns that point to rows in the same table. 131 132 Chapter 13 Self Joins and Views Let s look at the data in this Personnel table EmployeelD EmployeeName ManagerlD 1 Susan Ford NULL 2 Harold Jenkins 1 3 Jacqueline Baker 1 4 Richard Fielding 1 5 Carol Bland 2 6 Janet Midling 2 7 Andrew Brown 3 8 Anne Nichol 4 9 Bradley Cash 4 10 David Sweet 5 The ManagerlD column tells which manager the employee reports to. The ID number in this column corresponds to the numbers in the EmployeelD column. For example Harold Jenkins has a ManagerlD of 1. This indicates that .