Demonstrates how to return data from two tables by joining them together. Explains the assumptions the database makes in order to affect the merge
- [Woman Instructor] Sometimes you need data from more than one table. So let's say we want a report of all customers who are living in Buenos Aires. We can't return that data just from the customer table because the customer table doesn't store the address. So what we need to do is merge or join data from two tables. The basic structure of a join is straight forward. You've got select fields, in this case everything, from table one, join table two, and then we use ON to stipulate how we want the database to knit the data together, and when we run that we are returned all five fields from the customer table, followed by all 12 fields from the address table.
So it's given us everything from both tables side by side. Let's have a look at this query in a bit more detail. After the ON keyword, we supply the two field names that we want to merge on, and we want to merge on address_id, which is the primary key of one table and a foreign key in the other. But we couldn't just say ON address_id equals address_id because if we run that, we'd get an error that says the address_id is ambiguous, which indeed it is.
The data base doesn't know whether we mean the address_id in the customer table or in the address table. And that's why we use that dot notation, customer. and address. to tell the database go and find the customer table within which find the address_id column, and then ditto, find the address table within which find the address_id column. Note in passing that we could use back ticks here in any combination by the way. So I could add one here and one down there, and the whole thing would work just the same.
We can also use table aliases to make the query a bit neater. So we can say customer AS c, address AS a, and then we can repeat those aliases further down which makes everything a bit more easy to read. These can also be written without the AS keyword, by the way, like so. So just using a space and that works too.
When we use join in this way, we're returned every row that has a record in both the customer table and the address table. So here we've got a mocked up small version of the customer table containing five rows, and the address_id column has got one, two, three, six, and 18 as values, and then over here we have the address table which has got six rows in it and that's got one, two, three, four, five, and six. And four and five are grayed out because they don't appear in the customer table, and in the customer table 18 is grayed out because that doesn't appear in the address table.
So despite the fact that we're joining a five row table to a six row table, the output is only four rows long because there's only four rows that appear in both tables. There are other joins available and we'll be covering those shortly. We could look at the same information in a venn diagram. We could say we have table A and table B, and the only rows that are going to be returned are those that occur in both A and B, this blue section in the middle here.
Now, we wanted originally, customers with an address of Buenos Aires, so we need to filter with WHERE, but where does WHERE go? JOIN and ON should be read as part of the FROM section of the query. So WHERE should therefore come after. So we can say WHERE district is Buenos Aires. And there we have just 10 records returned, which is correct because we know there are 10 rows against Buenos Aires.
If we just want name and address, we could put the field names up here, c.first_name, c.last_name, a.address. Now if the field is unique such as the district field, you don't need to prefix it with an alias. Generally speaking, if I've gone to the trouble of declaring aliases, I then use them on everything because it's quicker to use them than to work it out, which ones you need and which ones you don't.
But technically you don't need them. So there we have it, all of the customers are returned who are living in Buenos Aires.
Join Emma Saunders as she shows you how to design and write simple SQL queries for data reporting and analysis. Review the different types of SQL, and then learn how to filter, group, and sort data, using built-in SQL functions to format or calculate results. Learn a bit about data types and database design. Discover how to perform more complex queries, such as joining data together from different database tables. Last but not least, Emma shows how to save your queries as views, so you can run them again and again.
- Using different versions of SQL
- Retrieving data with SELECT statements
- Filtering and sorting your results
- Transforming results with built-in SQL functions
- Grouping SQL results
- Merging data from multiple tables
- Identifying data types, and how to make sense of your database design
- Saving SQL queries