270 Part I EXAM PREPARATION IN THE FIELD
270 Part I EXAM PREPARATION IN THE FIELD UNION OPERATORS AND DATABASE DESIGN Recall for a moment Chapter 2, Database Modeling. Remember that the elements that make up a database are called tables, and tables should classify entities. A person is an entity; a company is an entity; a sale of a product is another entity. If you have one table for companies and a separate table for individual people, and you use those tables to track sales, it s pretty natural to write a query that would union the table of people with the table of companies to provide a summary report or even just a list of mailing addresses for a new company catalog. On the other hand, if you have several different tables of company information, and you re joining the tables together with a UNION, you may have something wrong with your database design. Whenever you use a UNION, ask yourself why the tables on which you are using the UNION are separate tables. Is it because they represent distinct entities, or because the database isn t designed the way it should be? In general, an overuse of the UNION operator is indicative of bad design. Objects that are so alike in structure that they can be joined with a UNION should in all probability be in the same table in the first place. One unique feature of the UNION is that it automatically removes duplicates from the final resultset. So, if the Table1 and Table2 tables have rows that are identical, SQL Server automatically filters them all out. Of course, this does have a great deal of overhead associated with it. To avoid that overhead, if you don t care about duplicates, you can use the UNION ALL command, like this: SELECT A, B, C FROM Table1 UNION ALL SELECT First, Second, Third FROM Table2 You can now write queries that join data horizontally across columns with the various JOIN clauses and that join data vertically across rows with the UNION operator. You ve also packed a few additional tools such as table aliases and derived tables into your toolbox. These are all foundational components for the next piece, grouping data.
For reliable and cheap web hosting services please check cheap web hosting website.