Y<\/code>. We will use these tables for some simple example queries before moving to the Northwind tables.<\/p>\nCREATE TABLE X (\r\nXid int CONSTRAINT PKX PRIMARY KEY\r\n);\r\nCREATE TABLE Y (\r\nYid int CONSTRAINT PKY PRIMARY KEY,\r\nXid int --should be an FK constraint\r\n--but left off for examples.\r\n);\r\n\r\nINSERT INTO X (Xid)\r\nVALUES (1),(2),(3);\r\n\r\nINSERT INTO Y (Yid, Xid)\r\nVALUES (1,2),(2,3),(3,3),(4,4);<\/pre>\nAfter creating the tables and executing the INSERT<\/code> statements, the tables have the following data.<\/p>\nTable X:<\/p>\n
\n\n\nXid<\/strong><\/td>\n<\/tr>\n\n| 1<\/td>\n<\/tr>\n | \n| 2<\/td>\n<\/tr>\n | \n| 3<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Table Y:<\/p>\n \n\n\nYid<\/strong><\/td>\nXid<\/strong><\/td>\n<\/tr>\n\n| 1<\/td>\n | 2<\/td>\n<\/tr>\n | \n| 2<\/td>\n | 3<\/td>\n<\/tr>\n | \n| 3<\/td>\n | 3<\/td>\n<\/tr>\n | \n| 4<\/td>\n | 4<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Since table Y<\/code> references table X<\/code> (commonly referred to as being a child of table X<\/code>), we need to connect the data using the value in the Y<\/code> table that references the data in table X<\/code>.<\/p>\nPutting the tables side by side, matching on the Xid column using the join criteria<\/strong> is X.Xid = Y.Xid<\/code>, we can see how the data matches:.<\/p>\n\n\n\nX.Xid<\/strong><\/td>\nY.Yid<\/strong><\/td>\nY.Xid<\/strong><\/td>\n<\/tr>\n\n| 1<\/td>\n | \u00a0<\/td>\n | \u00a0<\/td>\n<\/tr>\n | \n| 2<\/td>\n | 1<\/td>\n | 2<\/td>\n<\/tr>\n | \n| 3<\/td>\n | 2<\/td>\n | 3<\/td>\n<\/tr>\n | \n| 3<\/td>\n | 3<\/td>\n | 3<\/td>\n<\/tr>\n | \n| \u00a0<\/td>\n | \u00a0<\/td>\n | 4<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Note that for Xid = 3, two Y rows match one X row, so that data has been duplicated. The queries we will write will connect the two tables in variations of this pattern.<\/p>\n Join Styles<\/h3>\nYou can specify join criteria in the WHERE<\/code> or FROM<\/code> clause of a statement. Although both approaches can produce the same results in certain situations, they differ in functionality and recommended usage.<\/p>\n\n- Explicit Join:<\/strong> Using the
JOIN<\/code> keyword to connect objects to join, you specify the join join criteria in an ON<\/code> clause. This is known as explicit join notation<\/strong>, which involves using the ON<\/code> keyword to specify the predicates or conditions for the join and the JOIN<\/code> keyword to indicate the tables to be joined.\nThis approach explicitly defines how the tables relate, improving query readability and organization. For more complex queries, evaluating the join conditions early can allow the database engine to optimize the query execution plan, potentially leading to enhanced performance.<\/p>\n Syntax:<\/li>\n<\/ul>\n SELECT column_names\r\nFROM Table1\r\n JOIN Table2\r\n ON Table1.column_name = Table2.column_name<\/pre>\n \n- Implicit Join:<\/strong> In this notation, the tables to be joined are listed in the
FROM<\/code> clause of the SELECT<\/code> statement, separated by commas. The join predicates are then specified in the WHERE<\/code> clause. This method has limitations; it does not support OUTER<\/code> joins naturally. If one is not cautious, it can easily produce results equivalent to a CROSS<\/code> join, especially when the join criteria are missed or mistaken. it can result in less readable and less optimized queries, particularly when working with large datasets or complex joins.\nSyntax:<\/li>\n<\/ul>\n SELECT column_names\r\nFROM Table1, Table2\r\nWHERE Table1.column_name = Table2.column_name<\/pre>\nRelationships<\/h3>\nNext, let’s explore SQL relationships essential for comprehending joins. Joins allow us to merge data from multiple tables based on their relationships. Relationships play a pivotal role in organizing and connecting data between different tables.<\/p>\n Relationships allow you to facilitate normalization, as they enable you to store related information in separate tables and eliminate duplicated data. Joins will allow you to re-combine the data as needed during queries for usage.<\/p>\n They essentially define how data in one table is related to data in another table. The three fundamental types of relationships are one-to-one, one-to-many, and many-to-many. In the diagram, the end with the \u201ccrow\u2019s foot\u201d represents the relationship where one or more rows can be related:<\/p>\n ![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/10\/word-image-98851-2.jpeg\") <\/p>\n
Source: Author<\/p>\n \n- One-to-One Relationship<\/strong>: In relational database design, a one-to-one (1:1) relationship signifies that a record in Table A is associated with zero or one record in Table B (note that one table will need to be considered the parent in the relationship due to how relationships are implemented.). In this relationship, each record in Table A corresponds to a single record in Table B, and no duplicate values are allowed. One-to-one relationships are typically used when data needs to be separated into multiple tables for normalization or security purposes.<\/li>\n
- One-to-Many Relationship<\/strong>: In relational database design, a one-to-many (1:N) relationship signifies that one instance of Table A can be associated with zero, one, or more instances of Table B. In comparison, each instance of Table B can relate to zero or one instance of Table A. One-to-many relationships are widely used to represent hierarchical or parent-child relationships between data.<\/li>\n
- Many-to-Many Relationship<\/strong>: In relational database design, a many-to-many (M:N) relationship means multiple records in Table A can associate with multiple records in Table B and vice versa. Specifically, an instance from Table A can relate to zero, one, or more instances of Table B, and similarly, an instance from Table B can link to zero, one, or more instances of Table A.<\/li>\n<\/ul>\n
When working with joins, it’s essential to understand the relationships that the designer intended between the tables you are joining thoroughly. This includes recognizing both matching and non-matching rows or instances between the tables. The presence or absence of multiple matches can influence the outcome of the join operation. Being attentive to these details ensures accurate interpretation of the results and maintains data integrity.”<\/p>\n SQL join types play a crucial role in determining the treatment of matching and non-matching rows during the join operation. Let’s explore a concise summary of the distinctions between various join types.<\/p>\n Join Type Overview<\/h3>\nIn this section I will go describe each of the join types, and then in the next section we will work through some examples.<\/p>\n Inner Join<\/h4>\nAn inner join returns only the rows with matching values in the specified column(s) from both tables. It combines data from two tables based on the common column(s) and eliminates non-matching rows.<\/p>\n Syntax:<\/p>\n SELECT * \r\nFROM X\r\nINNER JOIN Y\r\nON X.Xid = Y.Xid;<\/pre>\nUsing the tables we created earlier. This query will return:<\/p>\n \n\n\nxid<\/strong><\/td>\nyid<\/strong><\/td>\nxid<\/strong><\/td>\n<\/tr>\n\n| 2<\/td>\n | 1<\/td>\n | 2<\/td>\n<\/tr>\n | \n| 3<\/td>\n | 2<\/td>\n | 3<\/td>\n<\/tr>\n | \n| 3<\/td>\n | 3<\/td>\n | 3<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Looking at the columns, they will be based on the table to the left of the join first, then the tables from the right column. Note that xid<\/code> = 1 does not show up in the results for the X<\/code> table and xid = 4<\/code> does not show up from the Y<\/code> table..<\/p>\nLeft Outer Join:<\/h4>\nLeft outer join returns all rows from the left table (Table1<\/code>) and the matching rows from the right table (Table2<\/code>). Non-matching rows in the right table will contain NULL<\/code> values in the result set.<\/p>\nSyntax:<\/p>\n SELECT * \r\nFROM X\r\nLEFT JOIN Y\r\nON X.Xid = Y.Xid<\/pre>\nNow the results are the same, except there is one additional row returned. The X<\/code> row where xid = 1<\/code> will show up now with NULL<\/code> values for all of the values for the Y<\/code> table’s columns.<\/p>\n\n\n\nxid<\/strong><\/td>\nyid<\/strong><\/td>\nxid<\/strong><\/td>\n<\/tr>\n\n| 2<\/td>\n | 1<\/td>\n | 2<\/td>\n<\/tr>\n | \n| 3<\/td>\n | 2<\/td>\n | 3<\/td>\n<\/tr>\n | \n| 3<\/td>\n | 3<\/td>\n | 3<\/td>\n<\/tr>\n | \n| 1<\/td>\n | \u00a0<\/td>\n | \u00a0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nRight Outer Join<\/h4>\nRight outer join returns all rows from the right table (Table2<\/code>) and the matching rows from the left table (Table1<\/code>). Non-matching rows in the left table will contain NULL<\/code> values in the result set. If you change the join to be a RIGHT JOIN<\/code>:<\/p>\nSELECT * \r\nFROM X \r\nRIGHT JOIN Y \r\nON X.Xid = Y.Xid;<\/pre>\nThe output now includes the Y<\/code> row where xid = 4<\/code>.<\/p>\n\n\n\nxid<\/strong><\/td>\nyid<\/strong><\/td>\nxid<\/strong><\/td>\n<\/tr>\n\n| 2<\/td>\n | 1<\/td>\n | 2<\/td>\n<\/tr>\n | \n| 3<\/td>\n | 2<\/td>\n | 3<\/td>\n<\/tr>\n | \n| 3<\/td>\n | 3<\/td>\n | 3<\/td>\n<\/tr>\n | \n| \u00a0<\/td>\n | 4<\/td>\n | 4<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nFull Outer Join:<\/h4>\nFull outer join returns all rows from both tables, including matching and non-matching rows.\u00a0<\/p>\n Syntax:<\/p>\n SELECT * \r\nFROM X\r\nFULL OUTER JOIN T\r\nON X.Xid = Y.Xid;\r\n<\/pre>\nNow you will see we are back to the version of the output that was shown when I showed you the data side by side.<\/p>\n \n\n\nxid<\/strong><\/td>\nyid<\/strong><\/td>\nxid<\/strong><\/td>\n<\/tr>\n\n| 2<\/td>\n | 1<\/td>\n | 2<\/td>\n<\/tr>\n | \n| 3<\/td>\n | 2<\/td>\n | 3<\/td>\n<\/tr>\n | \n| 3<\/td>\n | 3<\/td>\n | 3<\/td>\n<\/tr>\n | \n| 1<\/td>\n | \u00a0<\/td>\n | \u00a0<\/td>\n<\/tr>\n | \n| \u00a0<\/td>\n | 4<\/td>\n | 4<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n This is very often used to find rows in two tables where some condition is not true in two tables. Like in this case I can find all rows where X.Xid<\/code> is not in Y<\/code> and Y.Xid<\/code> is not in X<\/code> by adding where X.Xid is null or Y.Xid is null<\/code>. Then your output will be just he last two rows.<\/p>\nCross Join:<\/h4>\nCross join (also known as Cartesian join) returns the Cartesian product of both tables. It combines each row from the first table with every row from the second table, resulting in a potentially large result set. One of the more common uses of the CROSS JOIN<\/code> is to add the contents of a single row to a lot of rows. Note that there is no join criteria, and every row in one input matches every row in the other.<\/p>\nSyntax:<\/p>\n SELECT * \r\nFROM X\r\nCROSS JOIN Y;<\/pre>\nThe output of this query a lot longer than the others, because it will return (number of rows in X) * (number of rows in Y) rows. In this case 3 * 4 or 12 rows.<\/p>\n \n\n\nxid<\/strong><\/td>\nyid<\/strong><\/td>\nxid<\/strong><\/td>\n<\/tr>\n\n| 1<\/td>\n | 1<\/td>\n | 2<\/td>\n<\/tr>\n | \n| 1<\/td>\n | 2<\/td>\n | 3<\/td>\n<\/tr>\n | \n| 1<\/td>\n | 3<\/td>\n | 3<\/td>\n<\/tr>\n | \n| 1<\/td>\n | 4<\/td>\n | 4<\/td>\n<\/tr>\n | \n| 2<\/td>\n | 1<\/td>\n | 2<\/td>\n<\/tr>\n | \n| 2<\/td>\n | 2<\/td>\n | 3<\/td>\n<\/tr>\n | \n| 2<\/td>\n | 3<\/td>\n | 3<\/td>\n<\/tr>\n | \n| 2<\/td>\n | 4<\/td>\n | 4<\/td>\n<\/tr>\n | \n| 3<\/td>\n | 1<\/td>\n | 2<\/td>\n<\/tr>\n | \n| 3<\/td>\n | 2<\/td>\n | 3<\/td>\n<\/tr>\n | \n| 3<\/td>\n | 3<\/td>\n | 3<\/td>\n<\/tr>\n | \n| 3<\/td>\n | 4<\/td>\n | 4<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n Note too that the following will output the same results:<\/p>\n SELECT * \r\nFROM X,Y;<\/pre>\nAnd this too, because just like the CROSS JOI<\/code>N, every row matches every other row.:<\/p>\nSELECT * \r\nFROM Y\r\nINNER JOIN X\r\n ON 1=1; --any criteria that is always true<\/pre>\nJoin Subtypes<\/h4>\nThe following examples are syntaxes but are types of joins you can do that are interesting to understand.<\/p>\n Self Join<\/strong><\/p>\nA self join involves joining a table to itself, treating it as two separate instances. It allows for comparing and combining rows within the same table based on specified column(s).<\/p>\n Syntax:<\/p>\n SELECT * \r\nFROM X\r\nINNER JOIN X AS T2\r\nON X.Xid = T2.Xid;<\/pre>\nNote that if the column used is the PRIMARY KEY<\/code>, it will basically just duplicate all of the data in Table1<\/code> twice (as you would see if you executed the sample query). This type of query is typically done with things like employee and manager relationships.<\/p>\nFor example, if Table1<\/code> was Employee, with an EmployeeId<\/code> for the primary key value, you might have a column ManagerEmployeeId<\/code>and you would execute:<\/p>\nSELECT * \r\nFROM Employee\r\nINNER JOIN Employee AS Manager \r\n ON Employee.ManagerEmployeeId = Manager.EmployeeId<\/pre>\nNatural Join<\/strong><\/p>\nA natural join automatically matches columns with the same name in the two tables being joined. It eliminates the need to specify the join condition explicitly, assuming that column names and datatypes match accurately.<\/p>\n SELECT * \r\nFROM X\r\nNATURAL JOIN Y;<\/pre>\nThe output from this query will be just like the INNER JOIN example except for one thing. The column that is used for the join criteria (or criterion if you have multiple columns with the same name) will not be repeated. So the output will be:<\/p>\n \n\n\nxid<\/strong><\/td>\nyid<\/strong><\/td>\n<\/tr>\n\n| 2<\/td>\n | 1<\/td>\n<\/tr>\n | \n| 3<\/td>\n | 2<\/td>\n<\/tr>\n | \n| 3<\/td>\n | 3<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n <\/p>\n Note that in the example above, that is an INNER<\/code> join on the columns that match in X<\/code> and Y<\/code>. You can do a NATURAL OUTER JOIN<\/code>, or any of the other join types as well, the NATURAL<\/code> modifier just makes it use the columns from the tables.<\/p>\nJoin Examples<\/h2>\nMastering the art of joining tables is a fundamental skill for anyone working with relational databases. By understanding the general syntax, the underlying process, and the differences in join types, you can effectively combine data from multiple tables, extract meaningful insights, and unleash the power of your database management system. With this knowledge, you’ll be well-equipped to navigate the world of generic joins and leverage their capabilities to your advantage.<\/p>\n In this section, we will go through some examples using the Northwind database that we included instructions for early in the article.<\/p>\n Inner Join<\/h3>\nLet’s examine what a few tables look like that we are going to be joining together for an INNER JOIN<\/code>. First, the customer table looks like by returning a few rows from the table. Using pgAdmin, start a new query in the Northwind<\/code> database and execute the following query:<\/p>\nSELECT * \r\nFROM customers\r\nLIMIT 5;<\/pre>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/10\/word-image-98851-3.png\") <\/p>\n
Query results from PgAdmin, Source Author<\/p>\n This returns 5 rows from the table. For small databases on limited servers, this will almost always return the same data, but be aware that without an ORDER BY<\/code> clause, there is no guarantee of any order to the output.<\/p>\nNext, let us quickly examine the orders<\/code> table that we intend to join with the customers<\/code> table.<\/p>\nSELECT * \r\nFROM orders\r\nLIMIT 5;<\/pre>\nThis query will retrieve 5 rows from the orders<\/code> table and display all columns for each of those rows.<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/10\/word-image-98851-4.png\") <\/p>\n
Query results from PgAdmin, Source Author<\/p>\n We will now perform an INNER JOIN<\/code> between the orders table and the customers table, resulting in the following query:<\/p>\nSELECT c.customername, o.orderid, o.orderdate \r\nFROM customers c\r\n INNER JOIN orders o\r\n ON c.customerid = o.customerid\r\nLIMIT 5;<\/pre>\nThis query will retrieve a result set of five rows (the LIMIT<\/code> clause will cut off the output of rows at the number specified) that includes the customer\u2019s name, order ID, and order date for all customer orders in the Customers<\/code> table. Using an INNER JOIN<\/code>, only rows with matching customer IDs in the customer and orders tables will be included in the result set.<\/p>\n![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/10\/word-image-98851-5.png\") <\/p>\n
Query results from PgAdmin, Source Author<\/p>\n In summary, this query retrieves the customer name, order ID, and order date for the first five records where there is a matching customer ID between the customers and orders tables.<\/p>\n Inner joins can be used in various scenarios, from simple to complex queries involving multiple tables. They can also be combined with other SQL keywords, such as WHERE<\/code> clauses and aggregate functions, to refine the result set further. Understanding how inner joins work can help write efficient and effective queries.<\/p>\nLeft Outer Join<\/h3>\nThe LEFT OUTER JOIN<\/code>, also known as simply a left loin, is a join operation in database management systems that returns all the rows from the Table1 and the matching rows from the Table 2. The result will contain NULL<\/code> values if there is no match in the right table.<\/p>\nTo provide more context, we will utilize our Northwind database and leverage the existing customer and orders tables in the following manner:<\/p>\n SELECT c.customerid, c.customername, o.orderid, o.orderdate\r\nFROM customers c\r\nLEFT OUTER JOIN orders o\r\nON c.customerid = o.customerid;<\/pre>\nThis query will return a result set that includes the customer ID, customer name, order ID, and order date for all customers, whether they have placed an order or not. A subset of the rows that will be returned are shown here:<\/p>\n ![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/10\/word-image-98851-6.png\") <\/p>\n
Query results, Source Author<\/p>\n If you order by the customername<\/code> via the query<\/p>\nSELECT c.customerid, c.customername, o.orderid, o.orderdate\r\nFROM customers c\r\nLEFT JOIN orders o\r\n ON c.customerid = o.customerid\r\nORDER BY c.customername;<\/pre>\nYou will notice duplicates in the customername<\/code> column because multiple orders from the same customer are present in the orders<\/code> table. Since the query performs a LEFT JOIN<\/code> between the customers<\/code> and orders<\/code> tables, it retrieves all rows from the | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/10\/word-image-98851-1.png\")
<\/p>\n