Tutorials
Practical T-SQL
Practical T-SQL
  • Practical T-SQL Pocket Guide For Beginners
  • Preface
    • Section I. A Note From The Author
    • Section II. Tutorial Overview
    • Section III. Running The Examples
    • Section IV. How To Report An Issue
    • Section V. Join The MSU Community On Discord
    • Section VI. Supplementary Material
  • Language Basics
    • Lesson 1. Obligatory Hello World
    • Lesson 2. Code Comments With T-SQL
    • Lesson 3. Basic Syntax
    • Lesson 4. Your First Query
    • Lesson 5. Filtering Data
    • Lesson 6. Sorting Data
    • Lesson 7. Complex Data Filtering
    • Lesson 8. Aliases
    • Lesson 9. String Functions
    • Lesson 10. Creating New Columns From Existing Data (Calculated Fields)
    • Lesson 11. Displaying Data Based On Conditions (Case Statement)
    • Lesson 12. Aggregate Functions
    • Lesson 13. Grouping And Summarizing Data
    • Lesson 14. Querying More Than One Table
    • Lesson 15. Combining Queries
    • Lesson 16. Subqueries
    • Lesson 17. Creating Data
    • Lesson 18. Updating Data
    • Lesson 19. Deleting Data
    • Lesson 20. Common Table Expressions (CTEs)
    • Lesson 21. Derived Tables
    • Lesson 22. Putting It All Together
  • Advanced Topics
    • Lesson 23. Selecting Unique Values
    • Lesson 24. Updating Data With A Join
    • Lesson 25. Data Types
    • Lesson 26. Casting Data Types
    • Lesson 27. Creating Tables
    • Lesson 28. Altering Tables
    • Lesson 29. Dropping Tables
    • Lesson 30. Variables
    • Lesson 31. Controlling Flow
    • Lesson 32. Looping
    • Lesson 33. Error Processing
    • Lesson 34. Temporary Tables
    • Lesson 35. Views
    • Lesson 36. Indexed Views
    • Lesson 37. User Defined Functions
    • Lesson 38. Stored Procedures
    • Lesson 39. BULK INSERT
    • Lesson 40. Loading Tables With MERGE
    • Lesson 41. Partitioning A Dataset
    • Lesson 42. Pivoting Data
    • Lesson 43. Dynamic SQL
    • Lesson 44. Cursors
  • Solutions To Real World Problems
    • Lesson 45. Listing All Tables In A SQL Server Database
    • Lesson 46. Listing All Columns In A SQL Server Database
    • Lesson 47. Pull Records From A Table At Random
    • Lesson 48. A Better Alternative To WITH (NOLOCK)
    • Lesson 49. Boost Performance When Calling A Stored Proc From SSIS
    • Lesson 50. Setting Up Queries For Ablation Testing
    • Lesson 51. Reduce Code And Save Time With Default Column Values
    • Lesson 52. Finding Duplicate Records In A Table
    • Lesson 53. Why You Cannot Have More Than One Clustered Index On A Table
    • Lesson 54. Converting Dates To YYYYMMDD
    • Lesson 55. Sending Notification Emails With T-SQL Without Using Hardcoded Email Addresses
    • Lesson 56. Troubleshooting Long Running Queries
    • Lesson 57. Loading Large CSVs Into Data Warehouse Staging Tables
    • Lesson 58. The Only Bloody Good Reason To Use Cursors
    • Lesson 59. Loading A Type II Slowly Changing Dimension With SQL Merge
    • Lesson 60. A Clearer Explanation Of The Parameters Of The Numeric Data Type
    • Lesson 61. Why You Cannot Join On Null Values
    • Lesson 62. A Deep Dive On How The Where Clause Functions
    • Lesson 63. Using HASHBYTES() To Compare Character Strings
    • Lesson 64. Using Pipe To Hash Multiple Columns For Matching
    • Lesson 65. Why People That Indent Code Drive Me Nuts
    • Lesson 66. How To Rapidly Stand Up A Data Warehouse From Scratch
    • Lesson 67. How To Pivot Data With T-SQL When Columns Are Not Predefined
    • Lesson 68. Prepopulating A Junk Dimension
Powered by GitBook
On this page
  • JOIN Syntax
  • Examples
  1. Language Basics

Lesson 14. Querying More Than One Table

In this section, your query skills are going to take a quantum leap from interesting to useful.

Databases are collections of tables that are built in a manner such that they store data in the form of a relationship. Unless you are looking at a flat reporting table, the only way you are going to get what you need is by creating queries that combine information from more than one table.

There are many kinds of relationships in a database. Talking about them all is beyond the scope of this tutorial. We will talk about how to identify relationships so you can figure out how to query a table.

Table relationships are set up as parent child. In the most common example, the records in a parent table have one to many related records in a child table.

Relationships are defined by keys. In SSMS, you can expand the columns of a table and see that there will be a key icon denoting that column as a key of some sort. There are two kinds of keys you need to be aware of.

Primary keys. Denoted in SSMS by the PK indicator in the column display. These keys are unique identifiers of tables.

Foreign keys. Denoted in SSMS by the FK indicator in the column display. These keys are the primary keys of a child table’s parent table. If the database is designed properly, the column will be the same name in both tables.

You join tables by connecting them by their keys using the JOIN ON clause.

  1. JOIN the table you would like to add to the query.

  2. ON the primary key and foreign key of the tables.

Now you can see why aliases come in handy. When you join more than one table, you have to fully qualify column names so SQL Server knows which column in which table you are referring to. It would be annoying to have to write out the table name so many times.

There are different kinds of joins and more than one join syntax. From a syntax perspective, I will be teaching you my preferred way to write joins.

JOIN Syntax

SELECT *
FROM table1 t1
JOIN table2 t2
ON t1.KeyName = t2.KeyName

Examples

Inner Join

Returns only matching records between the parent and child. I write my statements so the parent table is above the child. That way I can quickly see what the relationship is.

USE AdventureWorks2016

SELECT *
FROM Sales.SalesOrderHeader soh
JOIN Sales.SalesOrderDetail sod
ON soh.SalesOrderID = sod.SalesOrderID

Left And Right Outer Join

The next two examples will demonstrate how to get all of the records from one table and only matching records from the other. This comes in handy when you want to see where there are records with no match or when the join between two tables is interesting but not necessarily an absolute reporting requirement.

The left and right notation refers to which table you want to return all records from. If you want all records from the left table, then you write the query so that table is on the left side of the equality in the ON clause and vice versa.

In the example below, you can see how the JOIN clause is modified. Technically, the word OUTER is not mandatory, but I write it anyway because on the job it really helps the JOIN clause stick out in a 1,000 line stored procedure.

Here I only show a LEFT OUTER JOIN because the way I write SQL statements makes writing a RIGHT OUTER JOIN a rare occurrence. Rare as in I cannot even think of a time when I did it.

USE AdventureWorks2016


SELECT 
p.FirstName,
p.LastName,
e.JobTitle,
e.HireDate
FROM Person.Person p
LEFT OUTER JOIN HumanResources.Employee e
ON p.BusinessEntityID = e.BusinessEntityID

Joins I Never Use

There are more joins that I wanted to make you aware of but, honestly, I rarely, if ever, use these joins in actual practice.

FULL OUTER JOIN – Get all the records from the left and right table regardless of any matching.

CROSS JOIN – This is what is called a Cartesian product. This type of join gets you every possible combination of join between two tables. Put another way, every single record in one table joined to every single record in another table. I actually will use CROSS JOIN from time to time. It comes in handy when you need to populate a junk dimension and you already know every single value that will go in the table.

Self-Join – This is more conceptual than an actual JOIN clause. Self joins exist, but, if you have to do them, then in all likelihood, the table you are working with was poorly designed.

PreviousLesson 13. Grouping And Summarizing DataNextLesson 15. Combining Queries

Last updated 3 years ago