• JOIN Columns from Different Tables
• Handle Query/Table Names with Spaces
• Handle Duplicate Column Names
• Combine Tables with FULL OUTER JOIN
• CROSS JOIN
• JOIN Queries Across Schema
• JOIN Queries Across Folders

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.

SELECT PhysicalExam.ParticipantId,
       PhysicalExam.weight_kg,
       Demographics.Gender,
       Demographics.Height
  FROM PhysicalExam INNER JOIN Demographics ON PhysicalExam.ParticipantId = Demographics.ParticipantId

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,
       "Physical Exam".weight_kg,
       Demographics.Gender,
       Demographics.Height
  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.

SELECT 
CASE 
	WHEN ViralLoad.ParticipantId IS NULL THEN ImmuneScore.ParticipantId
	WHEN ViralLoad.ParticipantId IS NOT NULL THEN ViralLoad.ParticipantId
	END AS
	ParticipantId,
CASE 
	WHEN ViralLoad.VisitID IS NULL THEN ImmuneScore.VisitID
	WHEN ViralLoad.VisitID IS NOT NULL THEN ViralLoad.VisitID
	END AS
	VisitID,
ViralLoad.RNACopies,
ImmuneScore.Measure1,
FROM ViralLoad
FULL OUTER JOIN ImmuneScore ON 
ViralLoad.ParticipantId = ImmuneScore.ParticipantId AND ViralLoad.VisitID = ImmuneScore.VisitID

CROSS JOIN

Cross joins give you all possible combinations between two different tables (or queries).

For example, suppose you have two tables Things and Items:

Then cross join will provide the set of all possible combinations between the two tables:

SELECT Things.Name AS Thing,
Items.Name AS Item
FROM Items
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.

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

lists.Languages

The following SQL JOIN creates a table that combines the data from both source tables.

SELECT Demographics.ParticipantId,
Demographics.Language,
Languages.TranslatorName,
Languages.TranslatorPhone,
Demographics.Gender
FROM Demographics
JOIN lists.Languages ON Demographics.Language=Languages.LanguageName

The data returned:

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:

SELECT Demographics.ParticipantId,
Demographics.Language,
Languages.TranslatorName,
Languages."PhoneNumber",
FROM Demographics
JOIN "/Other/Folder".lists.Languages ON Demographics.Language=Languages.Language

Learn more about queries across folders in this topic:

• Queries Across Folders

Related Topics

• LabKey SQL Examples
• Queries Across Folders
• Lookups: SQL Syntax
• More LabKey SQL Examples
• Query Metadata