When our data was all in a single table, we could easily retrieve a particular row from that table in order to get all the pertinent data we needed for a particular query. For example, looking at our unnormalized table below, if we wanted information on the books that 'John Smith' has checked out, we could use a simple Show
Now that this data is split across three tables, What is a SQL Join?SQL handles queries across more than one table through the use of JOINs. JOINs are clauses in SQL statements that link two
tables together, usually based on the keys that define the relationship between those two tables. There are several types of JOINs: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS; they all do slightly different things, but the basic theory behind them all is the same. We'll take a look at each type of Join SyntaxThe general syntax of a
The ellipsis ( The first part of this:
is essentially the Let's first focus on the second part of the statement, the part that joins the tables:
To join one table to another, PostgreSQL needs to know several pieces of information:
These pieces of information are combined together using the Remember our If we wanted a list of shapes and their colors, we could use a query like this:
Within the second part of this query, With this virtual join table created, the
The resulting data would look like this: Now that we understand the basic principle behind how joins work, let's take a look at some specific examples of different types of joins. Types of JoinsAs mentioned earlier, a INNER JOINAn In the query below, the line
The data in our unjoined tables looks like this: The result of our The
value in the If we did want to include Jane Smith in our results despite her not having an address, we would have to use a different type of join, an outer join. LEFT JOINA LEFT JOIN or a LEFT OUTER JOIN takes all the rows from one
table, defined as the Let's try and use the same JOIN query as before, but this time we'll use a left join:
Here, the Note that using either RIGHT JOINA
The data in our unjoined tables looks like this: The result of our As you can see, FULL JOINA A Another uncommon type of
join is a CROSS JOINA The way this join works is
sometimes a little difficult to envisage, so it's worth looking at an example in this case. This SQL query has the similar syntax to other
The query above returns the In an application, it's very unlikely that you would use a Now that we've covered the basics of joins, let's explore a couple of other useful techniques for working with multiple tables. Multiple JoinsIt is possible, and indeed common, to join more than just two tables together. This is done by adding additional
Here we are using two To understand what's happening here, we have to dive a little deeper into how joins work. We won't go into too much depth, though - just enough to clarify how the above command works. When you
perform a If you add a second A third and fourth In the command shown above, the first AliasingYou may have noticed that some of the queries we list above can get a bit long. We can cut back on the length of these queries by using aliasing. Aliasing allows us to specify another name for a column or table and then use that name in later parts of a query to allow for more concise syntax. Let's use our three table join from above as an example. Using aliasing, the query would look like this:
Here we specify single letter aliases for our tables, and use those aliases instead of our table names in order to prepend the columns from those tables in the column list and in the join conditions. This is commonly referred to as 'table aliasing'. We can even use a shorthand for aliasing by leaving out the Column AliasingAliasing isn't just useful for shortening SQL queries. We can also use it to display more meaningful information in our result table. For instance, if we want to display the number of checkouts from the library we could write something like:
If we hadn't used aliasing above then we lose context about what was counted.
If you're a user just trying to access information, then most likely you wouldn't know about the exact tables being queried; being explicit about what information we're displaying can be important in a case like that. SubqueriesThus far in this chapter, we've looked at using Imagine
executing a For example, suppose we need to select users that have no books checked out. We could do this by finding
In
the code above, the This might seem a bit confusing, so let's break it down. Our initial The nested query This virtual table can then effectively be used by our The only value in that column that is not in the
results of the nested query is the Subquery Expressions PostgreSQL provides a number of expressions that can be used specifically with sub-queries, such as We won't go into too much detail about subqueries here as you'll get to work with them some more later in course LS180. One thing it is useful to know though is that in some situations a subquery can be used as an alternative to a join. Subqueries vs JoinsAs you write more queries, you may find that there is more than one way to write a query and achieve the same results. The most common choices are between subqueries and JOINs. For instance, we can get the same result table as in our previous example by using a
When creating queries that return the same result, a differentiator between them may be their performance when compared to each other. As a general rule, JOINs are faster to run than subqueries. This may be something to bear in mind if working with large datasets. SummaryWe've covered a lot of content in this chapter, from exploring how joins work at a conceptual level, through working with different types of joins, and finally to useful techniques such as aliasing and subqueries. One of the most important things to remember about how joins work is that we set a condition that compares a value from the first table (usually a primary key), with one from the second table (usually a foreign key). If the condition that uses these two values evaluates to true, then the row that holds the first value is joined with the row that holds the second value. Let's quickly recap on some of the different types of join we can use:
When using joins, sometimes our queries can get unwieldy, especially when we're dealing with 2 or more Finally, the result from a join query can sometimes be obtained using different methods. Subqueries offer another method for us to query the database and retrieve the same results of similar results, as if we had used a JOIN clause. We're almost finished with our Introduction to SQL. In the next and final chapter we'll sum up everything we've learned so far and point you towards some next steps and resources to continue your learning! Which join type will return all of the records from the right table and only matching information from the table on the left?A left join returns all records from the "left" table. A left join returns any rows from the "right" table that match the join condition. Rows from the "right" table that don't match the join condition are returned as NULL .
Which type of join is used to returns all rows from right table and only matched rows from left table?The LEFT JOIN returns all rows from the left table and the matching rows from the right table.
Which type of join contains all the records from one table and matching records from the other table?A CROSS JOIN , also known as a Cartesian JOIN, returns all rows from one table crossed with every row from the second table. In other words, the join table of a cross join contains every possible combination of rows from the tables that have been joined.
Which join returns all records when there is a match in either left or right table?The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.
What type of join is used when you are looking for only matching records?SQL outer join
On joining tables with a SQL inner join, the output returns only matching rows from both the tables. When using a SQL outer join, not only it will list the matching rows, it will also list the unmatched rows from the other tables.
|