This topic covers using JOIN in LabKey SQL to bring data together. All data is represented in tables, or queries. A list or dataset or user defined query are all examples of queries. Queries are always defined in a specific folder (container) and schema, but can access information from other queries, schema, and folders using JOINs.
JOIN Columns from Different Tables
Use JOIN to combine columns from different tables.
The following query combines columns from the Physical Exam and Demographics tables.
FROM PhysicalExam INNER JOIN Demographics ON PhysicalExam.ParticipantId = Demographics.ParticipantId
Note that when working with datasets in a study, every dataset also has an implied "Datasets" column that can be used in select statements instead of join. See an example in this topic: More LabKey SQL Examples
Handle Query/Table Names with Spaces
When a table or query name has spaces in it, use "double quotes" around it in your query. For example, if the table name "Physical Exam" had a space in it, the above example would look like:
SELECT "Physical Exam".ParticipantId,
FROM "Physical Exam" INNER JOIN Demographics ON "Physical Exam".ParticipantId = Demographics.ParticipantId
Handle Duplicate Column Names
When joining two tables that have some column names in common, the duplicates will be disambiguated by appending "_1", "_2" to the joined column names as needed. The first time the column name is seen in the results, no number is appended.
See an example here: More LabKey SQL Examples
Combine Tables with FULL OUTER JOIN
The following example shows how to join all rows from two datasets (ViralLoad and ImmuneScore), so that the columns RNACopies and Measure1 can be compared or visualized. In this example, we handle the duplicate column names (ParticipantId and VisitID) using CASE/WHEN statements, though given the join is always on these two columns matching, we could also just select from either table directly.
WHEN ViralLoad.ParticipantId IS NULL THEN ImmuneScore.ParticipantId
WHEN ViralLoad.ParticipantId IS NOT NULL THEN ViralLoad.ParticipantId
WHEN ViralLoad.VisitID IS NULL THEN ImmuneScore.VisitID
WHEN ViralLoad.VisitID IS NOT NULL THEN ViralLoad.VisitID
FULL OUTER JOIN ImmuneScore ON
ViralLoad.ParticipantId = ImmuneScore.ParticipantId AND ViralLoad.VisitID = ImmuneScore.VisitID
Cross joins give you all possible combinations between two different tables (or queries).
For example, suppose you have two tables Things
Then cross join will provide the set of all possible combinations between the two tables:
SELECT Things.Name AS Thing,
Items.Name AS Item
CROSS JOIN Things
JOIN Queries Across Schema
You can join tables from two different schemas in the same data source by using the syntax "JOIN <SCHEMA>.<TABLE>" to refer to the table in another schema.
you cannot create joins across data sources. To join native tables to data from an external schema, you must first ETL
the external data into the local database. Learn more here:
For example, suppose you have the following tables in your local database, one in the study schema, the other in the lists schema:study.Demographics
|English||Monica Payson||(206) 234-4321|
|French||Etiene Anson||206 898-4444|
|German||Gundula Prokst||(444) 333-5555|
The following SQL JOIN creates a table that combines the data from both source tables.
JOIN lists.Languages ON Demographics.Language=Languages.LanguageName
The data returned:
|PT-101||English||Monica Payson||(206) 234-4321||M|
|PT-102||French||Etiene Anson||206 898-4444||F|
|PT-103||German||Gundula Prokst||(444) 333-5555||F|
JOIN Queries Across Folders
You can join queries from two different containers by adding the server path to the syntax in your JOIN statement. For example, if you had a project named "Other" containing a folder named "Folder" you could use a list in it from another folder by following this example:
JOIN "/Other/Folder".lists.Languages ON Demographics.Language=Languages.Language
Learn more about queries across folders in this topic: