Note I use the term JOIN instead of INNER JOIN because both are exactly the same in SQL.įrom the previous query you already know how to create the JOIN clause. Your Next Join: the Cities Included in Travel Packs Cheaper Than $2500Īgain, similar to the previous query, you can see you need to access two tables for this query, using a JOIN for the CITIES and TRAVEL_PACK tables again. JOIN TRAVEL_PACK ON CITIES.PACK_NAME = TRAVEL_PACK.PACK_NAMEįollowing is the result of the previous query: NAME Then the clause will be:įinally if you put all the clauses together the query will be: In the WHERE clause you will need to filter the results to include only beach cities. This is a good practice to apply when the query involves more than one table. Note the reference to the column PACK_NAME is done by adding a prefix with the table name ( TRAVEL_PACK in this case). The SELECT clause is easy because you only need to return the column NAME, then the clause will be: Once you complete the FROM clause with the INNER JOIN, you can continue working on the SELECT and WHERE clauses of the query. INNER JOIN TRAVEL_PACK ON CITIES.PACK_NAME = TRAVEL_PACK.PACK_NAME If you fill the pattern with your sample tables and column names, then you will have the next INNER JOIN clause: INNER JOIN table2 ON common column in table1 = common column in table2 Now, I will show the syntax to implement an INNER JOIN, by using the next pattern The next image shows how the JOIN clause creates a pair of records: However, in this article I will focus on the INNER JOIN, which is really powerful! You can learn many of these SQL join types in the SQL JOINs online course. For those readers who want to go deeper, there are other SQL join types, for example the opposite of the INNER join is another join type called LEFT join or OUTER LEFT join, and you can also find other join types like RIGHT join, NATURAL join and LATERAL join among others. This kind of join is called an INNER JOIN, and in SQL the terms JOIN or INNER JOIN are exactly the same. Basically it takes two records (one from each table) and joins them into a pair of records. The SQL JOIN acts as a connector between two tables, creating pairs of records. When you have to obtain data from two (or more) tables, you must use an SQL JOIN. So, the data needed for this query are in two tables, which is the reason that you need to use an SQL JOIN. In this case it is clear that you will need the TRAVEL_PACK table to obtain the names of the packs, but at the same time you will also need the CITIES table, because you need to check if the city is a beach city or not. I usually recommend starting out by thinking about where the data are located that you need for the query. Your First SQL JOIN: Obtain the Travel Packets Including Beach Cities Anyway, remember the column name PACK_NAME, because you will need it later to write the JOIN clause. In other words, when using an INNER JOIN clause the name of the common column can be different in both tables or not. It is easy to see that both columns have the same column name, however in other databases it can be different. As you can see in the TRAVEL_PACK table, the name of the pack is under the column PACK_NAME, and in the CITIES table the name of the pack is in the column PACK_NAME too. In the sample database this field is the name of the travel packet. Please note: In order to use a JOIN clause to combine two tables, there must be a field or column which appears in both tables. ![]() In the following image you can see the database, of course not all data are being shown. ![]() Suppose you work for a travel agency, and the agency database has two tables: TRAVEL_PACK and CITIES. Perhaps you already know that in a database the data are stored in tables.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |