Selecting From Multiple Tables

SQL Join Types

When pulling data from more than one table you will need to JOIN the tables together. The JOIN types will determine what records will be returned in the result set.

INNER JOIN – This type of JOIN will return only results that have a matching record in both tables. (For example, this will only display Customers who have placed an Order.) Notice that Customer 267 Bob Smith is not included in the results. This is because Bob had not placed any orders. Also, notice that Orders 290 and 402 do not appear in the results because they did not have any matching customers.

LEFT OUTER JOIN – This will return ALL the records from the Parent or Left table as well as the records that match from the Child or Right table. (This will display all the Customers whether they placed an order or not.) Notice that the Left Outer Join includes the customer 267 Bob Smith.

RIGHT OUTER JOIN – This will return ALL the records from the Child or Right table as well as the records that match from the Parent or Left table. (This will display all the Orders whether there was a Customer for that order or not.) A record in a Child table that does not have a matching record in the Parent table is called an Orphan record. In this example, the Right Outer JOIN displays the orders for 290 and 402. These are examples of Orphan records.

FULL OUTER JOIN – This will return ALL the records from both the Parent and Child tables.

Be the first to comment on "Selecting From Multiple Tables"

Leave a comment

Your email address will not be published.


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.