SQL Join Tables

10 Key excerpts on "SQL Join Tables"

• 

  eBook - ePub

  Database Management Systems

  • Sotirios Zygiaris(Author)
  • 2018(Publication Date)
  • Emerald Publishing Limited

    (Publisher)

  In Chapter 4, the queries were always applied to a single table. Since a major advantage of the relational model is to relate tables, these table relationships may be used to query the database in conjunction with two or more tables. The importance of relating information between different entities is profound in business. The theoretical basis of relating tables is as per relational algebra, especially the matrix theory. In related tables, the identification of relationships is not enough to establish a common data set between them.

  In relational algebra, merging tables means table multiplication, producing the Cartesian product. Thus, if TableA has 10 rows and TableB has 12 rows, the resulting merged table TableA-Times-TableB will have 10 * 12 = 120 rows. To understand the cross join merging mechanism, we illustrate the following case for the performance bonus assignment.

  The JOIN SQL operator joins two or more SQL-related tables. The Join operator merges data from two or more tables by meeting common values in common attributes (INNER JOIN) or by also including NULL values (OUTER JOIN) in the extracted table. The outer join returns not only the matching values between primary and foreign keys, but also the NULL unmatched values.

  This is important when queries require listing rows of tables that do not participate in the relationship. The OUTER JOIN could be LEFT, RIGHT, or FULL. A recursive join occurs when a table is joined to itself. Quite often, Join SQL statements can become extensive. To become more readable, aliases are used for the table names. Aliases are used as labels and DO NOT rename the tables. The aliases are valid only inside the query, while the table names remain unchanged.

  Apart from joining tables, another way to merge data is by using the set operators UNION, INTERSECT, and MINUS, based on a matrix or set mathematical theory. SQL manipulates data in sets by manipulating the data in two dimensions (columns and rows), which is a table. The following requirements apply when data are manipulated using SET operators.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Querying SQL Server

  Run T-SQL operations, data extraction, data manipulation, and custom queries to deliver simplified analytics (English Edition)

  • Adam Aspin(Author)
  • 2022(Publication Date)
  • BPB Publications

    (Publisher)

  Figure 2.3 illustrates what joining tables looks like.

  Figure 2.3: Representing table joins visually

  Conceptually, joining pairs of tables means knowing which fields can be used to create the “bridge” between the two tables. Once again, this could mean talking to the people who designed the database or delving into any documentation that you may have been given. Sometimes you can easily guess which fields can be used as joins, such as when the two fields have the same name or when the data is clearly the same in both tables. At other times it can be really difficult to guess the join fields just by looking at the tables. For the moment, I will explain how the sample tables are joined each time to avoid you having to guess.

  Practically, this code snippet takes three keywords that you already know well (SELECT, FROM, and ORDER BY) and extends the FROM clause with two new keywords: INNER JOIN and ON . These keywords allow you to create links between tables so that you can display fields from both tables at once. Linking (or joining) two tables in SQL consists of the following steps:

  1. Enter the name of the first table to join after the FROM keyword.
  2. Add the INNER JOIN keyword after the table name.
  3. Enter the name of the second table to join.
  4. Add the ON keyword.
  5. Enter the names of the two fields that are the link between the two tables, separated by the equal (=) sign.

  Each of the fields used to link the tables must be preceded by the name of the table that contains the field followed by a period. This enables SQL Server to identify which field can be found in which table when creating the link.

  Tricks and Traps

  As joining tables is a core concept, you should note these key points from the start:

  • The fields that you use to join the two tables may—or may not—have the same name in both tables. If the two field names are different, then you can just enter them “as is,” separated by an equal sign without adding the table name. If, however, the two fields have the same name, then you need to precede each field name by the table name and a period. This is because SQL Server gets confused if there is no way of uniquely identifying the field name—almost as if it doesn’t know which one to take to finish the join. Adding the table name and a period like this allows SQL to trace the field name back to its source table and consequently identify the correct field to use in the join.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Querying MySQL

  Make your MySQL database analytics accessible with SQL operations, data extraction, and custom queries (English Edition)

  • Adam Aspin(Author)
  • 2022(Publication Date)
  • BPB Publications

    (Publisher)

  Figure 2.3 illustrates what joining tables looks like.

  Figure 2.3: Representing table joins visually

  Conceptually, joining pairs of tables means knowing which fields can be used to create the “bridge” between the two tables. Once again, this could mean talking to the people who designed the database or delving into any documentation that you may have been given. Sometimes you can easily guess which fields can be used as joins, such as when the two fields have the same name or when the data is clearly the same in both tables. At other times it can be really difficult to guess the join columns just by looking at the tables. For the moment, I will explain how the sample tables are joined each time to avoid you having to guess.

  Practically, this code snippet takes three keywords that you already know well (SELECT, FROM, and ORDER BY) and extends the FROM clause with two new keywords: JOIN and USING. These keywords allow you to create links between tables so that you can display columns from both tables at once. Linking (or joining) two tables in SQL consists of the following steps:

  First	Enter the name of the first table to join after the FROM keyword.
  Second	Add the JOIN keyword after the table name.
  Third	Enter the name of the second table to join to the first.
  Then	Add the USING keyword and a left parenthesis.
  Finally	Enter the name of the field that is the link between the two tables followed by a right parenthesis.

  Tricks and Traps

  As joining tables is a core concept, you should note these key points from the start:

  • This particular join technique will only work when the two tables have a field with the same name in both tables that is used to join the tables. If this is not the case you will need to apply the technique described in the following section.
  • The order in which you enter the tables in the FROM clause is unimportant. You could write the FROM clause in this example as follows without altering the result: stock JOIN model

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Querying MariaDB

  Use SQL Operations, Data Extraction, and Custom Queries to Make your MariaDB Database Analytics more Accessible (English Edition)

  • Adam Aspin(Author)
  • 2022(Publication Date)
  • BPB Publications

    (Publisher)

  Figure 2-3 illustrates what joining tables looks like.

  Figure 2-3: Representing table joins visually

  Conceptually, joining pairs of tables means knowing which fields can be used to create the “bridge” between the two tables. Once again, this could mean talking to the people who designed the database or delving into any documentation that you may have been given. Sometimes you can easily guess which fields can be used as joins, such as when the two fields have the same name or when the data is clearly the same in both tables. At other times it can be really difficult to guess the join columns just by looking at the tables. For the moment, I will explain how the sample tables are joined each time to avoid you having to guess.

  Practically, this code snippet takes three keywords that you already know well (SELECT, FROM, and ORDER BY) and extends the FROM clause with two new keywords: JOIN and USING. These keywords allow you to create links between tables so that you can display columns from both tables at once. Linking (or joining) two tables in SQL consists of the following steps:

  First	Enter the name of the first table to join after the FROM keyword.
  Second	Add the JOIN keyword after the table name.
  Third	Enter the name of the second table to join to the first.
  Then	Add the USING keyword and a left parenthesis.
  Finally	Enter the name of the field that is the link between the two tables followed by a right parenthesis.

  Tricks and Traps

  As joining tables is a core concept, you should note these key points from the start:

  • This particular join technique will only work when the two tables have a field with the same name in both tables that is used to join the tables. If this is not the case you will need to apply the technique described in the following section.
  • The order in which you enter the tables in the FROM clause is unimportant. You could write the FROM clause in this example as follows without altering the result:   stock JOIN model

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  SQL in 7 Days

  A Quick Crash Course in Manipulating Data, Databases Operations, Writing Analytical Queries, and Server-Side Programming (English Edition)

  • Alex Bolenok(Author)
  • 2022(Publication Date)
  • BPB Publications

    (Publisher)

  In the relational data model, you do not make the relationships that you need. All possible relationships are already there. You discard those that you do not need. Think about it. Think about it hard. Write it down in your notebook and underline it twice. This is what SQL is about.

  You have an infinite sea of literally all possible relationships between the things that you model. You are not building them; they are already there; you are limiting them. By using clever mathematical formulas in your query, you can make exactly the right data surface from the depths of this bottomless sea and come to life.

  You are Michelangelo, the database is your block of stone, and SQL is the chisel you use to strip the data of its shell. It is trapped in the database, and your job is to set it free and show the world its beauty.

  ANSI join syntax

  Making a Cartesian product of two or more tables and then limiting it using a WHERE clause to express a relationship of some kind between their records is called joining the tables.

  You can come up with literally any crazy WHERE clause which could limit the tuple combinations from different tables. If it is a valid expression, it would parse and work.

  However, most cases are quite simple. Usually, you use a table to store some facts about the little world you are modeling. "There is this doohickey I own ", "here's this sale I made ", "that is some work I did ".

  Facts like this account for probably 99% of things people store in the database. When you store such a fact as a record in the database table, you give it an identifier of some kind, to tell it apart from the other facts. This is what the primary key does. You also write down something interesting about the facts. What the doohickey is called, how much the sale was, what the work was about. This kind of stuff.

  The kind of WHERE condition you use to limit the Cartesian set of two tables is called a join expression .

  Usually, what you want is "some field from one table equals to some field from another table ". This is called an equijoin

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Querying Databricks with Spark SQL

  Leverage SQL to query and analyze Big Data for insights (English Edition)

  • Adam Aspin(Author)
  • 2023(Publication Date)
  • BPB Publications

    (Publisher)

  Due to these architectures, database designers often need to add fields that allow tables to be linked, as you will see throughout this chapter. These fields are called key fields and may contain numbers or alphanumeric codes. These keys allow data to be mapped across tables so that information can be reconstituted harmoniously and coherently from several tables.

  Another reason for storing data in separate tables is that the data has come from disparate sources for analysis. So, you must meld the data together by linking (or joining) the data from different tables.

  To get used to the concept of using multiple tables, this chapter will show you how to:

  • Join two or more tables.
  • Select data from multiple tables.
  • Remove duplicates from the output.

  Now it is time to move on to the practice of the real-world SQL that you will need to master. This chapter teaches you how to use the links between tables to write more complex and powerful queries. This is an essential step in enabling you to deliver coherent source data analysis.

  This chapter and those that follow will use a different set of tables, not just the Allsales table you have used in this book. Consequently, as Appendix A explains, you must ensure you have loaded all the source tables.

  In this chapter, you will be required to forget the AllSales table you have used until now in this book. Instead, you will be using multiple smaller relational tables as the source of your queries.

  Joining tables

  As an initial step on your path to analyzing the data for Prestige Cars Ltd., you want to produce a complete list of every vehicle purchased and the amount paid to purchase it. After looking at the tables in the PrestigeCars database, you have found the stock table containing every car’s price. However, it does not contain the model but only an internal ID number that means nothing to you. However, you have also found a table called model, and this table contains the list of all the types of models that the company sells, as well as what appears to be the same ID number used in the stock table. You can see this represented graphically in Figure 10.1

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Learn SQL Database Programming

  Query and manipulate databases from popular relational database servers using SQL

  • Josephine Bush(Author)
  • 2020(Publication Date)
  • Packt Publishing

    (Publisher)

  Querying Multiple Tables

  In this chapter, you will learn how to query multiple tables. You will learn how to use SQL joins to join two or more tables together, including INNER and OUTER (LEFT, RIGHT, and FULL) joins, and advanced joins (cross, natural, and self joins). You will learn about set theory and how to combine queries using UNION and UNION ALL, and how to get the differences and intersections of different queries. Lastly, you will learn how to optimize queries when they contain multiple tables. In this chapter, we will cover the following topics:

  • Understanding joins
  • Using INNER JOIN
  • Using OUTER JOIN
  • Using advanced joins
  • Understanding set theory
  • Using indexes with your queries

  Passage contains an image

  Technical requirements

  You can refer to the code files of this chapter at the following GitHub link: https://github.com/PacktPublishing/learn-sql-database-programming/tree/master/chapter-7

  Passage contains an image

  Understanding joins

  Before we begin a discussion on the types of joins, let's go over what a join is and why you would use one. A join refers to when you connect two or more tables in a query. Joining tables in a query requires you to join them on a related column that is in each table you want to join together. There are a couple of different types of joins, including the following ones:

  • Inner join : This type of join returns only matching records from each joined table.
  • Outer join : This type of join has a few types of joins that can be used, including the following:
  • Left outer join : This type of join includes all rows from the left table and any matching rows between the left and right tables.
  • Right outer join : This type of join includes all rows from the right table and any matching rows between the right and left tables.
  • Full outer join : This type of join includes all rows from both the left and right tables. This type of join is not available in MySQL.

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Introductory Relational Database Design for Business, with Microsoft Access

  • Jonathan Eckstein, Bonnie R. Schultz(Authors)
  • 2017(Publication Date)
  • Wiley

    (Publisher)

  State field value does not match the condition. When comparing text fields to literal character strings such as “CA”, you should enclose the literal character strings in double quotes. Otherwise, SQL will try to interpret the character string as an attribute name.

  Inner Joins

  So far, this chapter has considered only queries drawn from a single table. We now discuss how SQL can express queries based on data from multiple tables. The most common technique for basing queries on multiple tables is called an inner join. An inner join consists of all combinations of rows selected from two tables that meet some matching condition, formally called a join predicate. One standard syntax for an inner join, of which we have already seen examples earlier in this book, is:

  First_Table INNER JOIN Second_Table ON Condition

  Formally, this syntax specifies that the query should form a table consisting of all combinations of a record from First_Table with a record from Second_Table for which Condition evaluates to “true.” Most frequently, Condition specifies that a foreign key in one table should match a primary key in the other.

  Here is an example of an inner join based on the plumbing store database:

  SELECT FirstName, LastName, OrderDate FROM CUSTOMER INNER JOIN ORDERS ON CUSTOMER.CustomerID = ORDERS.CustomerID;

  The INNER JOIN expression is now the data_source following the FROM keyword, where before we used a single table. This construction means that the data to be displayed is taken from the temporary table resulting from the inner join operation. The particular inner join expression, namely,

  CUSTOMER INNER JOIN ORDERS ON CUSTOMER.CustomerID = ORDERS.CustomerID

  specifies that the query should be based on all combinations of records from the CUSTOMER and ORDERS tables that have matching CustomerID fields. This kind of primary key to foreign key matching condition is by far the most common kind of inner join. CUSTOMER.CustomerID refers to the CustomerID field from the CUSTOMER table, while ORDERS.CustomerID refers to the CustomerID field from the ORDERS table. The use of “. ” here is called qualification. It is required to eliminate ambiguity whenever several underlying tables have fields of the same name, as is the case for the CustomerID in this example: if we were to just write CustomerID , SQL would not be able to tell whether we were referring to the CustomerID field in the CUSTOMER table or the CustomerID field in the ORDERS table. To resolve this ambiguity, we preface an attribute name with a table name and “. ”: for example, CUSTOMER.CustomerID means the CustomerID

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  Beginning Microsoft SQL Server 2012 Programming

  • Paul Atkinson, Robert Vieira(Authors)
  • 2012(Publication Date)
  • Wrox

    (Publisher)

  UNION operator, which allows you to combine the results of two queries into one.

  COMBINING TABLE DATA WITH JOINS

  When you’re operating in a normalized environment, you’ll frequently run into situations in which not all of the information that you want is in one table. In other cases, all the information you want returned is in one table, but the information you want to place conditions on is in another table. This is where the JOIN clause comes in.

  A JOIN does just what it sounds like — it joins the information from two tables together into one result set. You can think of a result set as being a virtual table. It has both columns and rows, and the columns have data types. Indeed, in Chapter 7 , I’ll show you how to treat a result set as if it were a table and use it for other queries.

  How exactly does a JOIN put the information from two tables into a single result set? Well, that depends on how you tell it to put the data together — that’s why there are four kinds of JOIN s. The thing that all JOIN s have in common is that they match one record up with one or more other records to make a record that is a superset created by the combined columns of both records.

  For example, take a look at a record from a table called Films :

  FILMID	FILMNAME	YEARMADE
  1	My Fair Lady	1964

  Now follow that up with a record from a table called Actors :

  FILMID	FIRSTNAME	LASTNAME
  1	Rex	Harrison

  With a JOIN , you can create one record from these two records found in totally separate tables:

  This JOIN (at least apparently) joins records in a one-to-one relationship. One Films record joins to exactly one Actors record.

  Let’s expand things just a bit and see if you can see what’s happening. I’ve added another record to the Actors

  Sign up to read

  Learn more about book
• 

  eBook - ePub

  SQL Pocket Primer

  • Oswald Campesato(Author)
  • 2022(Publication Date)
  • Mercury Learning and Information

    (Publisher)

  If you are not convinced of the preceding statement, consider this scenario: you have great performance in your SQL statements, but you aren’t sure if all the data is correct. If you have mission critical data that requires 100% data integrity, then data integrity has a higher priority than optimal performance.

  Fortunately, performance issues can sometimes be addressed by performing the appropriate denormalization of relevant tables. Note that this will involve rewriting the SQL statements that perform a JOIN of the normalized tables so that the new SQL statements query the single denormalized database table.

  Types of SQL JOIN Statements

  The JOIN keyword enables you to define various types of SQL statements that have slightly different semantics:

  • INNER JOIN
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • CROSS JOIN
  • SELF-JOIN

  Let’s suppose that table A has some (but not all) corresponding rows in table B , and that table B has some (but not all) corresponding rows in table A . Moreover, let’s also assume that a JOIN statement specifies table A first and then table B .

  An INNER JOIN returns all rows from table A that have non-empty matching rows in another table.

  A LEFT JOIN returns all rows from left-side table A and either matching rows from the right-side table B or NULL if no matching rows in right-side table B .

  A RIGHT JOIN returns all rows from right-side table B and either matching rows from the left-side table A or NULL if no matching rows in table A .

  A CROSS JOIN is a Cartesian or “full” product of rows from left-side table A and right-side table B .

  A SELF JOIN joins a table to itself. A common use-case involves an employees table that contains a manager attribute for each employee. Given an employee in this table, find the value in the manager attribute for that employee, and then search the employees table a second time using the manager attribute.

  This sequence of steps can be repeated until the top-most employee does not have a manager (such as the CEO). Given an employee, the preceding sequence produces the management hierarchy from the employee to the topmost employee in a company (defined in the table).

  Sign up to read

  Learn more about book