Sql Select Where Match All From Another Table

SQL Joins

When our data was all in a single table, we could easily call up a special row from that board ready to get all the pertinent information we needed for a particular query. For example, looking at our unnormalized table below, if we wanted information connected the books that 'John Smith' has checked out, we could use a simple Blue-ribbon query such as SELECT * WHERE full_name = 'John David Smith'. This would return us the first two rows from that table, containing information such As the account book title and author, and checkout and return dates.

Now that this data is disconnected across tercet tables, users, books, and checkouts, we first have to join those tables together before we can quality the information that we need.

What is a SQL Sum?

SQL handles queries across more than one table through the use of JOINs. JOINs are clauses in SQL statements that link two tables conjointly, usually based on the keys that define the relationship between those two tables. There are several types of JOINs: Inward, LEFT OUTER, RIGHT OUTER, FULL Outside and Cut through; they all coif slightly different things, but the basic possibility behind them all is the Lapplander. We'll acquire a consider each type of JOIN in turn, only first lets look into the general syntax that JOIN statements partake in.

Join Syntax

The oecumenical syntax of a JOIN affirmation is as follows:

          SELECT table_nameN.column_name, ...        FROM table_name1        join_type JOIN table_name2                  Connected join_condition;                  

The ellipsis (...) in the above command format indicates that we can provide whatsoever number of columns with the table_nameN.column_name format.

The first part of this:

          Choice table_nameN.column_name...                  

is essentially the Quality column_list form that you've already seen in previous Choose queries, with the slight difference that column names are prepended away table names in the column list.

Let's first cente the second part of the statement, the take off that joins the tables:

          FROM table_name1 join_type JOIN table_name2           ON join_condition                  

To join single table to another, PostgreSQL of necessity to know several pieces of information:

• The mention of the initiatory table to join
• The type of articulation to use
• The name of the second table to juncture
• The union consideration.

These pieces of information are combined together using the Link up and ON keywords. The part of the statement that comes after the ON keyword is the join condition; this defines the logic by which a row in extraordinary table is joined to a dustup in another table. In most cases this join condition is created victimisation the primary key of one board and the foreign primal of the table we want to join it with.

Remember our colors and shapes deterrent example from the early chapter? The color_id column of the shapes table is a Foreign Key which references the id column of the colors table.

If we wanted a list of shapes and their colors, we could use a query wish this:

          SELECT colours.discolour, shapes.shape        FROM colours        JOIN shapes             ON colors.id = shapes.color_id;                  

Within the second part of this query, colors JOIN shapes Along colors.Idaho = shapes.color_id, the join condition testament consider each I.D. value in the colors table and set about to friction match it with a color_id value in the shapes table. If there is a match then those two rows are joined together to form a new row in a virtual table known as a join hold over. Since the id 1 for the color Red appears twice in the color_id column of our shapes table, this row of the colors table appears double in our virtual join table, joined to some Square and Star topology. Since the ID 3 for the coloring Orangish does not appear at all in the color_id column of our shapes table, this row of the colours table is omitted completely from our practical join table.

With this virtual join table created, the Choice column_list FROM part of our statement can and so be executed to select columns from this virtual table. Those columns could originally be from the first table or the second table; to deflect confusion, we therefore need to specify both the tabular array name and column nominate in our column list, in the form table_name.column_name1. Looking at our example, selecting columns from our essential fall in board is effectively the same as saying:

          SELECT colors.discolor, shapes.shape        FROM shapes_colors_join_table;                  

The subsequent information would look like this:

Straight off that we understand the fundamentals behind how joins make for, let's get a load at roughly specific examples of dissimilar types of joins.

Types of Joins

Eastern Samoa mentioned before, a JOIN statement can interpose various forms. To specify which type of join to use, you can add either INNER, Left-handed, RIGHT, FULL or CROSS just before the keyword Connect. We'll look at an example of each of those types of unite exploitation the tables in our sql_book database.

Privileged JOIN

An Inside Sum returns a result set that contains the familiar elements of the tables, i.e the intersection where they match on the joined condition. Interior JOINs are the well-nig frequently victimized JOINs; in fact if you don't specify a join case and simply use the JOIN keyword, then PostgreSQL will assume you want an inner join. Our shapes and colors example from in the beginning used an INNER Junction in this way.

In the query below, the stemma Intimate Link (addresses) ON (users.id = addresses.user_id) creates the intersection between the two tables, which means that the join table contains only rows where thither is a definite couple between the values in the two columns in use in the condition.

          SELECT users.*, addresses.*        FROM users        INNER Link addresses              ON users.id = addresses.user_id;                  

The data in our unjoined tables looks like this:

The result of our SELECT question using an Privileged JOIN would look like this:

The value in the id column of the users hold over for the drug user Jane Smith is 5; since this value does not come out in the user_id editorial of the addresses table, she is omitted altogether from the join table and so only 3 records are returned by the query.

If we did want to include Jane Smith in our results disdain her not having an come up to, we would have to use a different typewrite of join, an outer join.

Near JOIN

A LEFT JOIN or a LEFT OUTER JOIN takes all the rows from matchless table, defined as the LEFT postpone, and joins information technology with a second board. The Union is settled on the conditions supplied in the Connected clause. A LEFT JOIN volition forever include the rows from the Left-handed hold over, straight if there are no matching rows in the table it is JOINed with. When there is nary match, the corresponding rows will use NULL to represent the missing values from the second table.

Let's try and consumption the same Link up query as before, but this time we'll use a left join:

          SELECT users.*, addresses.*        FROM users        Leftfield Conjoin addresses             ON users.id = addresses.user_id;                  

Here, the Jane Smith row from the users table is included in the join table, since she doesn't consume any twin rows in the addresses table, the join table has NULL values in her row for the columns of that table.

Government note that using either Socialistic JOIN or LEFT OUTER Bring together does exactly the same thing, and the OUTER part is frequently omitted. Regular so, it is still vernacular to refer to this typewrite of join as an 'outer' juncture in order to differentiate it from an 'inmost' join. Another typecast of outer union is a Outside JOIN, which we'll consider incoming.

Just JOIN

A RIGHT JOIN is kindred to a Unexpended JOIN except that the roles between the two tables are reversed, and all the rows on the second table are included along with any matching rows from the initiatory table. In the last chapter we mentioned that in our sql_book database we have books, and also reviews for those books. Non all of our books have reviews, however. Let's make a Letter-perfect Bring together or In good order OUTER Conjoin that displays whol reviews and their associated books, on with any books that don't have a review. When there is no match, the corresponding rows will use NULL to represent the missing values from the first table.

          SELECT reviews.book_id, reviews.subject matter,        reviews.rating, reviews.published_date,        books.id, books.rubric, books.author     FROM reviews     RIGHT JOIN books           ON reviews.book_id = books.I.D.;                  

The data in our unjoined tables looks like this:

The result of our SELECT query using an RIGHT JOIN would look like this:

As you can see, My Tertiary SQL Word doesn't yet have a reexaminatio, and so all the columns from the review table have NULL values for that row in the join table.

Overflowing JOIN

A FULL JOIN operating theater FULL OUTER Conjoin is fundamentally a combination of LEFT JOIN and RIGHT JOIN. This type of join contains all of the rows from both of the tables. Where the join condition is met, the rows of the two tables are joined, even as in the late examples we've seen. For any rows along either side of the join where the join condition is not met, the columns for the other table have NULL values for that quarrel. When there is nobelium friction match, the corresponding rows bequeath use NULL to represent the nonexistent values.

A Rumbling JOIN is a trifle less average than the other ones we've looked at soh furthest and so we won't evince an deterrent example for this.

Another red-carpet type of join is a CROSS JOIN; let's get a load.

Hybridizing JOIN

A CROSS JOIN, also known equally a Cartesian JOIN, returns all rows from one table crossed with every course from the back table. In other words, the conjoin table of a grumpy get together contains all possible compounding of rows from the tables that have been joined. Since information technology returns each combinations, a CROSS JOIN does not postulate to pair rows using a join precondition, thus it does not have an ON clause.

The way this link whole kit and boodle is sometimes a little awkward to envisage, sol information technology's Charles Frederick Worth looking at an case therein case. This SQL inquiry has the similar syntax to other JOINs, but without the ON clause:

          SELECT * FROM users CROSS JOIN addresses;                  

The query above returns the addresses and users tables, fussy joined. The result set consists of every record in users mapped to all record in addresses. For 4 users and 3 addresses, we baffle a total of 4x3=12 records. In mathematical terms, this is the cross product of a set.

In an application, it's very unlikely that you would use a CROSS Joint. Most of the time, it's more than important to pair rows conjointly through a join condition in society to return a purposeful result. It's still important to be aware of Spoil JOINs however, since you may occasionally encounter them.

Now that we've encrusted the basics of joins, allow's explore a couple of other useful techniques for impermanent with treble tables.

Sevenfold Joins

It is possible, and so informal, to link more than sensible deuce tables unneurotic. This is finished by adding additional JOIN clauses to your SELECT statement. To join multiple tables in this fashio, there must be a synthetic family relationship between the tables up to my neck. One exemplar would be joining our users, checkouts, and books tables.

          SELECT users.full_name, books.title of respect,        checkouts.checkout_date     FROM users     INNER JOIN checkouts           Connected users.Gem State = checkouts.user_id     INNER JOIN books           Happening books.id = checkouts.book_id;                  

Here we are using two INNER Unites. One between users and checkouts and one betwixt checkouts and books. In both cases the JOIN is implemented away using the Primary Key of one table (either users or books) and the Adulterating Key for that table in the checkouts table.

To understand what's natural event here, we ingest to dive a little deeper into how joins work. We won't go into to a fault much depth, though - just decent to clear up how the above command works.

When you do a Conjoin between two tables, PostgreSQL creates a virtual hold over (we can also call it a transient prorogue) that contains data from both the underivative table and the tabular array known by the Junction. It's convenient to imagine of this virtual table as containing all columns from the records in the FROM table as fountainhead as all columns from the JOIN table. Before PostgreSQL displays the results, it picks outer hardly the columns that you've mentioned in the Superior statement.

If you add a bit JOIN, like we did above, PostgreSQL creates yet other virtual put of that contains entirely of the columns from the previous virtual prorogue as symptomless Eastern Samoa wholly of the columns from the twin rows in the 2nd JOIN put over. Note that we're not referring back to the FROM table Hera -- we're only working with a virtual put over and a JOIN table, so we don't need to consultation the FROM table at all.

A third and fourth Link up acts in the same way -- all works with the previous virtual table and adds information from matching rows in the JOIN tables.

In the control shown above, the first JOIN combines data from users and checkouts into a practical board that contains users.full_name and checkouts.checkout_date. The second JOIN combines the data from this essential table with the rubric chromatography column from the books table. Conjointly, the dominate creates a virtual table that contains the 3 columns we're displaying.

Aliasing

You may have noticed that some of the queries we lean above can get a little long. We can trim back on the length of these queries by using aliasing. Aliasing allows the States to specify another name for a pillar 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. Victimisation aliasing, the query would look like this:

          Choice u.full_name, b.rubric, c.checkout_date        FROM users AS u        INNER JOIN checkouts Equally c            ON u.Idaho = c.user_id        INNER JOIN books AS b            ON b.id = c.book_id;                  

Here we specialize singular letter aliases for our tables, and habit those aliases or else of our tabular array names in purchase order to prepend the columns from those tables in the column list and in the fall in conditions. This is usually referred to as 'set back aliasing'.

We can even utilization a shorthand for aliasing by departure out the American Samoa keyword wholly. FROM users u and FROM users AS u are eq SQL clauses.

Column Aliasing

Aliasing isn't just useful for shortening SQL queries. We can also use it to expose more meaningful information in our termination put over. For instance, if we want to display the keep down of checkouts from the library we could publish something like:

          SELECT count(I.D.) AS "Number of Books Checked Out"        FROM checkouts;                  

          Number of Books Checked Out -----------------------------                           4 (1 row)                  

If we hadn't utilised aliasing above then we lose circumstance about what was counted.

          Superior count(id) FROM checkouts;                  

                      count -------      4 (1 row)                  

If you're a substance abuser just trying to access entropy, and so most likely you wouldn't recognise about the exact tables being queried; being unambiguous about what information we'Re displaying buns exist important in a case equivalent that.

Subqueries

Thus far in this chapter, we've looked at exploitation JOINs to bring on with Thomas More than nonpareil table. Although connexion tables in concert is probably the most common elbow room of on the job with multiple tables, you fundament a great deal achieve the same results through use of a subquery. Before we compare subqueries and joins, Lashkar-e-Taiba's essay what a subquery is.

Imagine executing a SELECT question, then using the results of that SELECT query as a condition in some other SELECT query. This is named nesting, and the query that is nested is referred to as a subquery.

For instance, say we need to prime users that have no books checked knocked out. We could do this by finding users whose user_id is not in the checkouts table. If nary relation is institute, that would mean that the exploiter has non checked out any books.

          Pick out u.full_name FROM users u        WHERE u.ID NOT IN (            Prize c.user_id FROM checkouts c        );                  

                      full_name -------------  Beset Potter (1 row)                  

In the encrypt above, the NOT IN clause compares the current user_id to altogether of the rows in the resolution of the subquery. If that id number ISN't part of the subquery results, then the full_name for afoot row is added to the result set.

This might appear a bit puzzling, so let's break it down. Our first checkouts table looks like this:

The nested query Choose c.user_id FROM checkouts c returns the next results:

This realistic table fire then effectively be used away our Non IN clause as a list of values against which to check the values in the id column of our users table.

The only value in that editorial that is not in the results of the nested question is the I.D. for 'Chivy Potter': 3.

Subquery Expressions

PostgreSQL provides a number of expressions that can be used specifically with sub-queries, such as IN, NOT IN, ANY, SOME, and ALL. These all work slightly otherwise, only fundamentally they entirely compare values to the results of a subquery.

We won't get into too so much detail about subqueries present as you'll get to work with them some more later o in course LS180. 1 thing it is useful to jazz though is that in some situations a subquery can be used instead to a link up.

Subqueries vs Joins

As you write more queries, you may find that thither is more than one way to write a query and achieve the equal results. The virtually common choices are betwixt subqueries and JOINs.

For instance, we give the axe dumbfound the Lapp resolution table as in our late case by using a JOIN clause instead of a subquery.

          SELECT u.full_name        FROM users u        Port JOIN checkouts c ON u.id = c.user_id        WHERE c.user_id IS NULL;                  

                      full_name --------------  Harry Thrower (1 row)                  

When creating queries that return the Lapplander result, a differentiator between them Crataegus oxycantha follow their performance when compared to apiece other. As a general rule, JOINs are quicker to run than subqueries. This may cost something to bear in mind if working with large datasets.

Summary

We've covered a lot of easygoing in this chapter, from exploring how joins process at a conceptual level, through working with different types of joins, and in conclusion to useful techniques such as aliasing and subqueries.

Matchless of the most in-chief things to think of about how joins work is that we set a condition that compares a value from the first table (usually a primary winder), with one from the second postpone (usually a foreign key). If the condition that uses these two values evaluates to sincere, then the rowing that holds the first value is married with the row that holds the second value.

Let's quick recap on any of the different types of join we can use:

Join Typewrite	Notes
Inward	Combines rows from 2 tables whenever the join condition is met.
LEFT	Same as an privileged join, except rows from the for the first time table are added to the join table, regardless of the evaluation of the get together condition.
Decently	Same arsenic an inner join, except rows from the second shelve are added to the join table, regardless of the valuation of the join condition.
FULL	A combination of leftmost join and right conjoin.
Intersect	Doesn't use a connect condition. The join table is the result of matching all rowing from the first table with the second table, the cross product of whol rows across both tables.

When using joins, sometimes our queries can get unwieldy, peculiarly when we're dealing with 2 or more JOINs. To ameliorate manage this we can alias table and column names to shorten our query. We can also use aliasing to give more linguistic context about the query results.

Finally, the result from a join query behind sometimes represent 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 all over with our Introduction to SQL. In the close and final chapter we'll sum up everything we've learned so far and point you towards some next steps and resources to keep your learning!

Sql Select Where Match All From Another Table

Source: https://launchschool.com/books/sql/read/joins

Share this post