SQL英文版教学课件：6-Table Joins,a must

1、10/8/20221SQL(6)Table Joins, a must 10/8/20222Table Joins, a must All of the queries up until this point have been useful with the exception of one major limitation - that is, youve been selecting from only one table at a time with your SELECT statement. 10/8/20223Table Joins, a mustIt is time to in

2、troduce you to one of the most beneficial features of SQL & relational database systems - the Join. To put it simply, the Join makes relational database systems relational.Table Joins, a must Joins allow you to link data from two or more tables together into a single query result-from one single SEL

3、ECT statement.10/8/2022410/8/20225Table Joins, a must A Join can be recognized in a SQL SELECT statement if it has more than one table after the FROM keyword. SELECT “list-of-columns”FROM table1,table2WHERE “search-condition(s)”10/8/20226Table Joins, a must Joins can be explained easier by demonstra

4、ting what would happen if you worked with one table only, and didnt have the ability to use joins. This single table database is also sometimes referred to as a flat table. 10/8/20227Example:Lets say you have a one-table database that is used to keep track of all of your customers and what they purc

5、hase from your store:idfirstlastaddresscitystatezipdateitemprice10/8/20228Example:Everytime a new row is inserted into the table, all columns will be updated, thus resulting in unnecessary redundant data. Example:For example, every time Wolfgang Schultz purchases something, the following rows will b

6、e inserted into the table:10/8/2022910/8/202210Example:idfirstlastaddresscitystatezipdateitemprice10982WolfgangSchultz300N. 1st AveYumaAZ85002032299snowboard45.0010982WolfgangSchultz300N. 1st AveYumaAZ85002082899snow shovel35.0010982WolfgangSchultz300N. 1st AveYumaAZ85002091199gloves15.0010982Wolfga

7、ngSchultz300N. 1st AveYumaAZ85002100999lantern35.0010982WolfgangSchultz300N. 1st AveYumaAZ85002022900tent85.0010/8/202211Example:An ideal database would have two tables: One for keeping track of your customers And the other to keep track of what they purchase: 10/8/202212Next step Customer_info tabl

8、e:customer_numberfirstnamelastnameaddresscitystatezip10/8/202213Example:Purchases table:customer_numberdateitemprice10/8/202214BenefitNow, whenever a purchase is made from a repeating customer, the 2nd table, Purchases only needs to be updated! Weve just eliminated useless redundant data, that is, w

9、eve just normalized this database!10/8/202215Data normalizationData Normalization is a technique of database design that is used to get the tables in your database into at least the third normal form (3NF). Data normalizationBasically, this means that you want to eliminate the redundancy of non-key

10、data when constructing your tables. Each table should only have columns that depend on the primary key.10/8/20221610/8/202217Example:Notice how each of the tables have a common cusomer_number column. This column, which contains the unique customer number will be used to JOIN the two tables. Example:

11、Using the two new tables, lets say you would like to select the customers name, and items theyve purchased. Here is an example of a join statement to accomplish this:10/8/20221810/8/202219ExampleSELECT customer_info.firstname, customer_info.lastname, purchases.itemFROM customer_info, purchasesWHERE

12、customer_info.customer_number = purchases.customer_number; 10/8/202220ExampleThis particular Join is known as an Inner Join or Equijoin. This is the most common type of Join that you will see or use.10/8/202221ExampleNotice that each of the columns are always preceded with the table name and a perio

13、d. This isnt always required, however, it IS good practice so that you wont confuse which columns go with what tables. ExampleIt is required if the name column names are the same between the two tables.I recommend preceding all of your columns with the table names when using joins. 10/8/20222210/8/2

14、02223ExampleNote: The syntax described above will work with most Database Systems. However, in the event that this doesnt work with yours, please check your specific database documentation.10/8/202224Note on the ExampleAlthough the above will probably work, here is the ANSI SQL-92 syntax specificati

15、on for an Inner Join using the preceding statement above that you might want to try:10/8/202225Another ExampleSELECT customer_info.firstname, customer_info.lastname, purchases.itemFROM customer_info INNER JOIN purchasesON customer_info.customer_number = purchases.customer_number; 10/8/202226Another

16、example:SELECT employee_info.employeeid, employee_info.lastname, employee_issionFROM employee_info, employee_salesWHERE employee_info.employeeid = employee_sales.employeeid; 10/8/202227Note:This statement will select the employeeid, lastname (from the employee_info table), and the commission value (

17、from the employee_sales table) for all of the rows where the employeeid in the employee_info table matches the employeeid in the employee_sales table. 10/8/202228Review ExercisesWrite a query using a join to determine which items were ordered by each of the customers in the customers table. Select t

18、he customerid, firstname, lastname, order_date, item, and price for everything each customer purchased in the items_ordered table. 10/8/202229Review ExercisesRepeat exercise #1, however display the results sorted by state in descending order. 10/8/202230Table Join Exercise Answers Exercise #1 SELECT customers.customerid, customers.firstname, customers.lastname, items_ordered.order_date, items_ordered.item, items_ordered.price FROM customers, items_ordered WHERE customers.customerid = items_ordered.customerid;10/8/20223