Postgresql joins8/5/2023 Movies-# WHERE customers.id = rentals.customer_id Now you can construct a query that will show us all of the rentals and the names of the corresponding customers: Adding the table qualifier is required if a command involves two columns with identical names, but is useful in other cases. So, customers.id refers to the id column in the customers table and rentals.customer_id refers to the customer_id column in the rentals table. You may find it helpful to qualify each column name by prefixing it with the name of the corresponding table, followed by a period. Notice that this WHERE clause mentions two columns with similar names ( customer_id and id). Looking back at the previous query, you can see that the meaningful rows are those WHERE customers.id = rentals.customer_id. So, given a rentals row, we can find the corresponding customers row by searching for a customer where the customer ID is equal to rentals.customer_id. Each row in the customers table is uniquely identified by a customer ID. What is the real-world relationship between a rentals row and a customers row? Each row in the rentals table contains a customer ID. These seven rows were produced by joining the first row in the rentals table: Take a close look at the results of the previous query. An inner-join starts with a cross-join, and then throws out the rows that you don't want. The second type of join, the inner-join, is very useful. The result set contains 4 x 7 or 28 rows.Ĭross-joins are rarely useful?they usually don't represent real-world relationships. The rentals table contains four rows the customers table contains seven rows. You can see that PostgreSQL has joined each row in the rentals table to each row in the customers table. Tape_id | rental_date | customer_id | id | customer_nameĪB-12345 | | 1 | 4 | Wonderland, Alice N.ĪB-67472 | | 3 | 4 | Wonderland, Alice N. Movies=# SELECT rentals.*, customers.id, customers.customer_name To perform a cross-join, we simply list each table in the FROM clause: Id | customer_name | phone | birth_date | balance We'll cross-join the rentals and customers tables. If you are joining against a third table, PostgreSQL joins each row in the intermediate result with each row in the third table. In a cross join, PostgreSQL joins each row in the first table to each row in the second table to produce a result table. The most basic join type is a cross-join (or Cartesian product). PostgreSQL (and all relational databases) supports a number of join types. When you need to retrieve data from multiple tables, you join those tables. If you SELECT from the rentals table, you can see the tape ID and customer ID, but you can't see the movie title or customer name. You can see that each row in the rentals table refers to a tape ( tape_id) and to a customer ( customer_id). Notice that each row in the rentals table refers to a customer ( customer_id) and a tape ( tape_id)." There are three pieces of information that we need to record for each rental: the tape_id, the customer_id, and the date that the rental occurred. "When a customer comes in to rent a tape, we will add a row to the rentals table to record the transaction. Here's a description of the rentals table from earlier in this chapter: Most databases contain multiple tables and there are relationships between these tables. So far, all the queries that you've seen involve a single table.
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |