previousnext
Lesson 28
Courses / Software School Cuts
How to Combine SQL Tables Data Together with an Inner Join

Learn how to use Structured Query Language (SQL) to combine matching data from two separate tables.

In particular, you learn how to match rows in one table with rows in another, going by the foreign key in one table pointing to the primary key of another table. You learn the Inner Join kind.

Summary

Summary of SQL Joins and Foreign Keys

In this discussion, we explore how to identify what a customer, specifically David, ordered from a store using SQL and the concept of table relationships.

Key Concepts

  1. Orders Table: This table tracks all customer orders.

    • Example Query: To find out what David (with customer ID 3) has ordered: 
      SELECT * FROM orders WHERE customer_id = 3;

  2. Foreign Key: The customer ID in the orders table is a foreign key that links it to the primary key of the customers table, establishing a relationship.

  3. Separation of Data: Best practices dictate that related data should reside in separate tables to maintain database normalization, avoiding cluttering the orders table with customer details.

  4. Using Joins:

    • Inner Join: This SQL command allows us to combine data from both tables based on a related column (customer ID).
    • Example Query: 
      SELECT * FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id;
    • To avoid ambiguity when the same column name exists in both tables, prefix them with the table names: 
      SELECT * FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id WHERE customers.customer_id = 3;

  5. Multiple Orders: If a customer has made multiple orders, each order will appear on a separate row alongside the customer's information.

  6. Consolidation of Rows: While it's possible to consolidate results using the GROUP BY clause, it's not recommended if the goal is to retain detailed order information.

  7. View All Customer Orders: Removing the WHERE clause will display all orders alongside their respective customer information:

    SELECT * FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

Conclusion

Understanding SQL joins and foreign keys is crucial for retrieving and organizing related data from multiple tables efficiently. Proper use of these concepts allows for effective database management and data analysis.

Scroll down above for more

Video Transcript

How do we know let's say we have David here? Let's find out what he ordered. We have a table of orders. So that's like you're in a store You're gonna keep track of all the orders. Let's understand. Okay. What did What did he buy? Well, how would you do it? Well, easiest ways look like you get the his customer ID is free So you're gonna say, okay select all the columns from what's the table now? That's orders Where the customer ID notice we have the column customer ID in order and that's three Make sure usually we always make sure that the customer ID here points to the ID of the customer column So you can see David Robinson customer ID three. He only bought a monitor no more orders Now this thing here is called a foreign key When you have ID in another table That is mapped to this table here in this case. We have orders and we have a customer ID For the customer ID here is a foreign key that points to the primary key of the customer's table This is a very common pattern. You're gonna see all over the place We don't usually like mix Table data together Obviously, you could have the orders table have the first name last name age and so on but that that's not good practice at all We need to separate the data So we have a table dedicated for customers and one for orders And if you need information about the customer like their first name last name Whatever you just got a look at the foreign key here in the order stable and then go to the customer stable and find a primary Key that matches that in this case This case is with four right so John So I was like that Now I'm gonna talk about how to include that information obviously this is not useful if I'm going here, okay Customer repeat who is three? I don't know so I don't want to manually look up in the table customers I want this to already show me the first name last name is so on that's very common thing We do and that's called a join SQL join. We're joining tables together. Okay, so I'm gonna go here I'm gonna say inner join That's a kind of join right, okay? There are different kinds of joins, but this one is inner draw They're gonna match the customer ID in orders with the customer ID in customers So we can exactly know every single order for that customer and their information Okay, we got to say Okay, you want to join with what what what table customers table and then on that's a keyword You got to say how you want to match. Okay. I want to match the customer idea of one table that's orders with the Customer ID of the other but notice here Both tables have the same column name. How do I know which one is which so I gotta this is ambiguous So how to find a way of you know this ambiguous here So Alice I can prefix these with the table name. So okay, let's say customers dot customer ID Oh, okay, so you're saying the customer ID column from from the customers Thus match the orders Dot customer ID so that way you know, okay, it's this customer ID here from customers has to match the customer ID from orders Okay, now I'm gonna move the where to the ending okay Because of syntax, but make sure to move your where to the end After you did the inner joint thing Now if I do that oh What happened? Oh Same problem I had the ambiguous right Where a customer is free in this case. It doesn't really matter which one I pick So you can say here either customers or orders And there you go now what happens is it takes this record From orders and then it's kind of put side by side with what it matches for The customer stables as you can see this the left hand side is from orders and the right hand side is from customers Now I want to show you Customer for because customer for bought two things right if you notice from the order table You bought a keyboard and mouse. So if I use for We're gonna get two rows Right because we have the one for the keyboard and one for the mouse But observe that each of them on the right hand side they get their own Long copy of the customer row the single customer row for John appear twice because we have two rows from order So that's what usually happens Does anybody have any questions Okay, let's see Somebody sent a link to this court. Thank you Can we explore important or so that's me to tell can we do this think this situation I Know what I mean. Yes, you can group by things Depending on what your goal is right you can consolidate these into one row and I'm gonna show you Away just now But there's no need to How can I say Whether you consolidate things is independent of you having a joint table or not, right? So we can consolidate into single row But the point here is you don't want to consolidate because you're trying to figure out the orders Right if you consolidate into one you're gonna lose He bored in mouth what are you gonna do? We don't know the orders the point here is not to consolidate But if you need to consolidate in another situation as you can And I'll show you how to count things in a moment So if I remove the where This is gonna happen for every customer so we got all the orders in the customer information Side on the side, okay Now we can see all the orders and the customer what they bought Joining the two tables and this is not narrowing down to anything
See more
No comments yet (loading...)
No comments yet (loading...)
Did you like the lesson? 😆👍
Consider a donation to support our work:
See donation links