In the next chapter, we will explore other types of joins.- select all columns from the table called "table_name" FROM table_name - select all columns from the table called "table_name" SELECT * FROM table_name - select all columns from the table called "table_name" in the schema "schema_name SELECT * FROM schema_name. You also learned the most frequently used joins, the inner join. In this chapter you learned the basics of relationships between tables that is required to use joins. Which one should you use? Prefer joins because it more efficient. > and line_items.order_number = orders.idĪs you can see the result is the same as using the subquery. > where customers.id = orders.customer_id Here is the query: sqlite> select name, email To create this query, it helps to list all the table to be joined so that you can see the relationship between them. In the previous chapter on subqueries we had this query: sqlite> select name, email from customers where id IN (select customer_id from orders where id IN (select order_number from line_items where product_id = 1)) Change the name of the column from 'name' to 'product name'. The first column in the above output is the name of the product. This avoids people who are shopping our products getting confused by sudden changes in the price of the product they are about to buy. The price column in the products table can be changed by the user who owns it anytime. The reason is that once the line_items is created the price column will not change. You might be wondering why I picked line_items price column instead of products' price column. The final order_number = 1 condition filters the results only for that order. The condition relates the foreign key product_id in the line_items table to the primary key of products, id in the products table. This is the first condition of the WHERE clause. The foreign key in the products table user_id is the primary key in users table id. The WHERE clauses are separated by AND keyword to specify the relationships between the tables. We had to specify line_items.price to avoid ambiguity. We can do that by prefixing the price with the name of the table as follows: sqlite> select name, first_name, line_items.price, quantity from line_items, products, users So we need to explicitly indicate which table the price column we are interested in resides. We get this error because we have price column in line_times as well as the products table. > and line_items.product_id = products.id sqlite> select name, first_name, price, quantity from line_items, products, users Let's now join these three tables to get the data. Id product_id quantity price order_numberġ 1 4 10.0 1 3 2 1 40.0 1 2 3 10 10.0 1 4 4 15 30.0 1ġ 10 Rock 1 2 20 Sand 1 3 30 Carrot 1 4 40 Hammer 1 5 40 Tree 2 6 80 Cage 3 7 80 Cage Wire 3 8 80 Camel Rope 3ġ bugs bunny 2 daffy duck 3 porky pig 4 pluto dog 5 pluto pet 6 tasmanian devil 7 tweety bird 8 elmer fudd 9 speedy gonzales 10 yosemite sam 11 sylvester 12 bosko Before we join these tables, let's take a look at the data to help us form the query. The user name is stored in the users table and the quantity is in the line_items table. We know that product name and product price are stored in the products table. Let's consider an example where we need to display product name, user name, product price and quantity for an order with order_number = 1. You have to list all the tables and define the relationship between them. The query is different, but the output is the same in both cases. sqlite> select first_name as user_name, name as product_name, price as product_price from users, products WHERE users.id = er_id Here is the query we used in the previous article, you can compare both the queries and see the difference. The WHERE clause is replaced by ON keyword.
Instead of comma separating the tables as we saw in the previous article on join, here we have INNER JOIN between the table names. Here is an example: sqlite> select first_name as user_name, name as product_name, price as product_price from users INNER JOIN products ON users.id = er_id If you had two circles to represent two tables, the common overlapping area is the inner join of those two tables. It is called inner join because it is the intersection of the two tables. In the previous chapter on Joins, the join we used is called as equijoin or inner join.
SQLITE INNER JOIN ON SUBQUERY HOW TO
In this chapter, you will learn how to use inner joins.
0 kommentar(er)
