SQL Tutorial

Author

Amanda Ng

Basic query

SELECT, FROM, LIMIT

A typical starting structure when using SQL is SELECT variable FROM table. This query will give a table showing only the selected variables from the table. To select multiple variables, we can use a comma , to separate them. The shortcut for showing all variables is *.

To avoid overwhelming the reader, we can limit the output table to show only the top observations using LIMIT after the FROM clause. For example:

SELECT Age, Education, Triglyceride
FROM nhanes
LIMIT 5
5 records
Age Education Triglyceride
20 Some college or AA degree 78
59 College graduate or above 136
58 College graduate or above 128
67 Some college or AA degree 55
47 College graduate or above 52 
mydata %>%
  select(Age, Education, Triglyceride)%>%
  head(5)
  Age                 Education Triglyceride
1  20 Some college or AA degree           78
2  59 College graduate or above          136
3  58 College graduate or above          128
4  67 Some college or AA degree           55
5  47 College graduate or above           52

We can also specify to show only distinct rows with DISTINCT in the SELECT rows. However, you are only allowed to distinct on a single variable or DISTINCT *, such as

SELECT DISTINCT Age
FROM nhanes
LIMIT 10;
Displaying records 1 - 10
Age
33
46
21
80
60
30
53
25
51
78 
mydata %>%
  select(Age)%>%
  head(10)%>%
  distinct()
   Age
1   20
2   59
3   58
4   67
5   47
6   19
7   63
8   56
9   61
10  33

To show distinct rows across multiple selected variables, we need to perform a GROUP BY, which will be introduced later.

Create new variables with SELECT, AS

We can also rename the variable name using AS, such as

SELECT Age, Education AS Edu, Triglyceride
FROM nhanes
LIMIT 5
5 records
Age Edu Triglyceride
20 Some college or AA degree 78
59 College graduate or above 136
58 College graduate or above 128
67 Some college or AA degree 55
47 College graduate or above 52 
mydata %>%
  select(Age, Edu = Education, Triglyceride) %>%
  head(5)
  Age                       Edu Triglyceride
1  20 Some college or AA degree           78
2  59 College graduate or above          136
3  58 College graduate or above          128
4  67 Some college or AA degree           55
5  47 College graduate or above           52

To define new variables, we also do it within the SELECT clause such as

SELECT Age, Age + 10 AS age_10_years_later
FROM nhanes
LIMIT 5
5 records
Age age_10_years_later
20 30
59 69
58 68
67 77
47 57 
mydata %>%
  mutate(age_10_years_later = Age + 10)%>%
  select(Age, age_10_years_later)%>%
  head(5)
  Age age_10_years_later
1  20                 30
2  59                 69
3  58                 68
4  67                 77
5  47                 57

We can also define a constant variable across rows.

SELECT 2026 AS constant_year
FROM nhanes
LIMIT 5
5 records
constant_year
2026
2026
2026
2026
2026 
mydata %>%
  mutate(constant_year = 2026)%>% 
  select(constant_year)%>%
  head(5)
  constant_year
1          2026
2          2026
3          2026
4          2026
5          2026

We can also concatenate strings using ||.

SELECT Ethnicity||Marital_status AS combined_e_m
FROM nhanes
LIMIT 5
5 records
combined_e_m
Mexican AmericanNever married
Other HispanicMarried
Other HispanicMarried
non-Hispanic blackMarried
non-Hispanic AsianMarried 
mydata %>%
  mutate(combined_e_m = str_c(Ethnicity, Marital_status))%>% 
  select(combined_e_m)%>%
  head(5)
                   combined_e_m
1 Mexican AmericanNever married
2         Other HispanicMarried
3         Other HispanicMarried
4     non-Hispanic blackMarried
5     non-Hispanic AsianMarried

Conditional assignments with CASE WHEN

To assign value to a new variable based on some conditions, we use CASE WHEN... THEN... ELSE...END.

In this assignment, we transformed the coded Gender column into the text form column Gender_name where we assign the value Male when it is coded as 1, Female when it is coded as 2, and Unknown otherwise.

SELECT Gender,
      CASE WHEN Gender = 1 THEN 'Male'
          WHEN Gender = 2 THEN 'Female'
            ELSE 'Unknown'
        END AS Gender_name      
FROM nhanes
LIMIT 10
Displaying records 1 - 10
Gender Gender_name
1 Male
1 Male
2 Female
1 Male
1 Male
1 Male
1 Male
2 Female
2 Female
2 Female 
mydata %>%
  mutate(Gender_name = case_when(Gender == 1 ~ 'Male', 
                                 Gender == 2 ~ 'Female',
                                 TRUE ~ 'Unknown'))%>% 
  select(Gender, Gender_name)%>%
  head(10)
   Gender Gender_name
1       1        Male
2       1        Male
3       2      Female
4       1        Male
5       1        Male
6       1        Male
7       1        Male
8       2      Female
9       2      Female
10      2      Female

Filtering rows with WHERE

Similar to dplyr in R, we can also filter to restrict our output to only show observations that satisfy certain conditions using WHERE clause. Note that the WHERE clause always comes after FROM. To build condition statements, we can use

  • Logical operators: =, <, >, <=, >=, and !=.
  • Boolean operators: AND, OR, and NOT

In this example, we show the top 5 observations with age greater than 20.

SELECT *
FROM nhanes
WHERE Age > 20
LIMIT 5
5 records
SEQN Age Gender Ethnicity Citizenship Education Marital_status Household_size Annual_income Sedentary_min Height Weight Health_insurance Private_insurance Cancer Heart_attack Stroke Inc_exercise Dec_salt Dec_fat LDL_Cholesterol Triglyceride
93801 59 1 Other Hispanic Citizen by birth or naturalization College graduate or above Married 2 $100000 and Over 600 71 175 Yes NA No No No No Yes Yes 94 136
93823 58 2 Other Hispanic Citizen by birth or naturalization College graduate or above Married 2 Under $20000 180 64 170 No NA No No No Yes No Yes 156 128
93830 67 1 non-Hispanic black Citizen by birth or naturalization Some college or AA degree Married 2 $75000 to $99999 360 75 252 Yes NA No No Yes Yes Yes Yes 81 55
93840 47 1 non-Hispanic Asian Citizen by birth or naturalization College graduate or above Married 3 $100000 and Over 600 70 172 Yes Yes No No No Yes No No 104 52
93887 63 1 non-Hispanic white Citizen by birth or naturalization Some college or AA degree Divorced 1 $15000 to $19999 9999 70 170 Yes NA No Yes Yes No No No 95 102 
mydata %>%
  filter(Age > 20)%>%
  head(5)
   SEQN Age Gender          Ethnicity                        Citizenship
1 93801  59      1     Other Hispanic Citizen by birth or naturalization
2 93823  58      2     Other Hispanic Citizen by birth or naturalization
3 93830  67      1 non-Hispanic black Citizen by birth or naturalization
4 93840  47      1 non-Hispanic Asian Citizen by birth or naturalization
5 93887  63      1 non-Hispanic white Citizen by birth or naturalization
                  Education Marital_status Household_size    Annual_income
1 College graduate or above        Married              2 $100000 and Over
2 College graduate or above        Married              2     Under $20000
3 Some college or AA degree        Married              2 $75000 to $99999
4 College graduate or above        Married              3 $100000 and Over
5 Some college or AA degree       Divorced              1 $15000 to $19999
  Sedentary_min Height Weight Health_insurance Private_insurance Cancer
1           600     71    175              Yes              <NA>     No
2           180     64    170               No              <NA>     No
3           360     75    252              Yes              <NA>     No
4           600     70    172              Yes               Yes     No
5          9999     70    170              Yes              <NA>     No
  Heart_attack Stroke Inc_exercise Dec_salt Dec_fat LDL_Cholesterol
1           No     No           No      Yes     Yes              94
2           No     No          Yes       No     Yes             156
3           No    Yes          Yes      Yes     Yes              81
4           No     No          Yes       No      No             104
5          Yes    Yes           No       No      No              95
  Triglyceride
1          136
2          128
3           55
4           52
5          102

SQL provides the LIKE operator to compare a string to a pattern. The pattern is a quoted string and can include these special characters:

  • _ matches any single character
  • % matches any sequence of zero or more characters

This outputs all distinct Ethnicity which contains Hispanic in the entry.

SELECT DISTINCT Ethnicity
FROM nhanes
WHERE Ethnicity LIKE '%Hispanic%'
5 records
Ethnicity
non-Hispanic Asian
Other Hispanic
other non-Hispanic races
non-Hispanic black
non-Hispanic white 
mydata %>%
  filter(str_like(Ethnicity, '%Hispanic%'))%>%
  select(Ethnicity)%>%
  distinct()
                 Ethnicity
1           Other Hispanic
2       non-Hispanic black
3       non-Hispanic Asian
4       non-Hispanic white
5 other non-Hispanic races

ORDER BY

Using ORDER BY, we can order the rows in the table based on column(s) of your choice. By default, it orders the row in ascending order (ASC). If you want to show the row corresponding to the larger values as the top row, put DESC after your column name.

This outputs the top 5 distinct Age in descending order, the first row corresponds to the observation with largest age in the dataset.

SELECT DISTINCT Age
FROM nhanes
ORDER BY Age DESC
LIMIT 5
5 records
Age
80
79
78
77
76 
mydata %>%
  select(Age)%>%
  arrange(desc(Age))%>%
  distinct()%>%
  head(5)
  Age
1  80
2  79
3  78
4  77
5  76

Grouping and Aggregation

Sometimes, we are interested in the group level statistical summaries. We can use GROUP BY and aggregation functions to so do.

Here are some common aggregation functions in SQL:

  • AVG()
  • MAX()
  • MIN()
  • COUNT()
  • SUM()

Tips:

  • use COUNT(*) is you want to summarize number of rows in the table
  • use COUNT(DISTINCT variable) is you want to summarize number of distinct values in a variable

For example, this shows the mean age for each education groups.

SELECT Education, AVG(Age) AS avg_age
FROM nhanes
GROUP BY Education
7 records
Education avg_age
College graduate or above 49.73737
High school graduate/GED or equivalent 52.70408
9-11th grade 53.52830
Some college or AA degree 52.10156
Don’t Know 80.00000
NA 17.45161
Less than 9th grade 58.27500 
mydata %>%
  group_by(Education)%>%
  summarize(avg_age = mean(Age))
# A tibble: 7 × 2
  Education                              avg_age
  <chr>                                    <dbl>
1 9-11th grade                              53.5
2 College graduate or above                 49.7
3 Don't Know                                80  
4 High school graduate/GED or equivalent    52.7
5 Less than 9th grade                       58.3
6 Some college or AA degree                 52.1
7 <NA>                                      NA

Note that, when we use GROUP BY, we must include the grouping column in the SELECT clause. Any columns storing individual-level values should not be included in SELECT after grouping since we are collapsing the dataset into group level observations. In other words, you can only include the grouping column and aggregated measures applied on the grouping column.

You can also group by more than one column, such as:

SELECT Education, Gender, AVG(Age) AS avg_age
FROM nhanes
GROUP BY Education, Gender
Displaying records 1 - 10
Education Gender avg_age
High school graduate/GED or equivalent 2 53.38462
Less than 9th grade 2 59.76190
NA 1 17.52941
Some college or AA degree 2 52.66667
9-11th grade 2 54.07407
Some college or AA degree 1 51.37500
9-11th grade 1 52.96154
NA 2 17.35714
Don’t Know 1 80.00000
College graduate or above 1 51.21154 
mydata %>%
  group_by(Education, Gender) %>%
  summarise(avg_age = mean(Age, na.rm = TRUE))
`summarise()` has regrouped the output.
ℹ Summaries were computed grouped by Education and Gender.
ℹ Output is grouped by Education.
ℹ Use `summarise(.groups = "drop_last")` to silence this message.
ℹ Use `summarise(.by = c(Education, Gender))` for per-operation grouping
  (`?dplyr::dplyr_by`) instead.
# A tibble: 14 × 3
# Groups:   Education [7]
   Education                              Gender avg_age
   <chr>                                   <int>   <dbl>
 1 9-11th grade                                1    53.0
 2 9-11th grade                                2    54.1
 3 College graduate or above                   1    51.2
 4 College graduate or above                   2    48.1
 5 Don't Know                                  1    80  
 6 High school graduate/GED or equivalent      1    51.9
 7 High school graduate/GED or equivalent      2    53.4
 8 Less than 9th grade                         1    56.6
 9 Less than 9th grade                         2    59.8
10 Some college or AA degree                   1    51.4
11 Some college or AA degree                   2    52.7
12 <NA>                                        1    17.5
13 <NA>                                        2    17.4
14 <NA>                                       NA   NaN

When we use GROUP BY, we are defining the units of aggregation. A common misconception is that grouping by multiple columns creates separate groups for each column independently. In reality, grouping uses the combination of the columns to define each group. In this example,

  • Grouping by one column (e.g., Education) creates one group for each unique Education level.
  • Grouping by two columns (e.g., Education and Gender) creates one group for each unique pair of values.

As a result, the same Education level can appear in multiple rows because each row represents a different combination with the second grouping variable. For example:

  • Some college or AA degree + 1
  • Some college or AA degree + 2

both belong to the same Education category, but they are different groups because the Gender value changes.

Filter groups with HAVING

Similar to WHERE, HAVING allows us to filter rows in the table after the GROUP BY clause. Using WHERE after GROUP BY is invalid.

For instance, we only want the average age of observations with College graduate or above education.

SELECT Education, AVG(Age) AS avg_age
FROM nhanes
GROUP BY Education
HAVING Education = 'College graduate or above'
1 records
Education avg_age
College graduate or above 49.73737 
mydata %>%
  group_by(Education)%>%
  filter(Education == 'College graduate or above')%>%
  summarize(avg_age = mean(Age))
# A tibble: 1 × 2
  Education                 avg_age
  <chr>                       <dbl>
1 College graduate or above    49.7

If you want to filter rows such that the variable value is in a list of values, we can use IN and (your_list_of_values).

For instance, we only want to calculate the average ages of observations with College graduate or above, Some college or AA degree, and 9-11th grade respectively.

SELECT Education, AVG(Age) AS avg_age
FROM nhanes
GROUP BY Education
HAVING Education IN ('College graduate or above', 'Some college or AA degree', '9-11th grade')
3 records
Education avg_age
Some college or AA degree 52.10156
College graduate or above 49.73737
9-11th grade 53.52830 
mydata %>%
  group_by(Education) %>%
  summarise(avg_age = mean(Age, na.rm = TRUE)) %>%
  filter(Education %in% c(
    "College graduate or above",
    "Some college or AA degree",
    "9-11th grade"
  ))
# A tibble: 3 × 2
  Education                 avg_age
  <chr>                       <dbl>
1 9-11th grade                 53.5
2 College graduate or above    49.7
3 Some college or AA degree    52.1

Set operations on row level

SQL provides several set operations that allow you to combine results of two queries (or two datasets) on row level.

Note that an operand to a set operator must be a complete query. For instance, if we wanted to conduct SET_OPERATION on tables nhanes_set1 and nhanes_set2, we couldn’t just write (nhanes_set1) SET_OPERATION (nhanes_set2).

UNION

UNION returns all rows that appear in either of the two result sets. In the example below, we have nhanes_set1 containing information from participants with ID 93731, 93801,and 93823; and nhanes_set2 containing information from participant with ID 93823, 93830, and 93840. The result outputs 5 rows in total. Notice that participant 93823 appears twice since it exists on both nhanes_set1 and nhanes_set2. UNION automatically eliminates duplicates.

SELECT *
FROM nhanes_set1
UNION 
SELECT * 
FROM nhanes_set2
5 records
SEQN Age Gender Ethnicity Citizenship Education Marital_status Household_size Annual_income Sedentary_min Height Weight Health_insurance Private_insurance Cancer Heart_attack Stroke Inc_exercise Dec_salt Dec_fat LDL_Cholesterol Triglyceride
93840 47 1 non-Hispanic Asian Citizen by birth or naturalization College graduate or above Married 3 $100000 and Over 600 70 172 Yes Yes No No No Yes No No 104 52
93731 20 1 Mexican American Citizen by birth or naturalization Some college or AA degree Never married 5 $100000 and Over 360 73 202 Yes Yes No No No No No Yes 97 78
93830 67 1 non-Hispanic black Citizen by birth or naturalization Some college or AA degree Married 2 $75000 to $99999 360 75 252 Yes NA No No Yes Yes Yes Yes 81 55
93801 59 1 Other Hispanic Citizen by birth or naturalization College graduate or above Married 2 $100000 and Over 600 71 175 Yes NA No No No No Yes Yes 94 136
93823 58 2 Other Hispanic Citizen by birth or naturalization College graduate or above Married 2 Under $20000 180 64 170 No NA No No No Yes No Yes 156 128 
union(nhanes_set1, nhanes_set2)
   SEQN Age Gender          Ethnicity                        Citizenship
1 93731  20      1   Mexican American Citizen by birth or naturalization
2 93801  59      1     Other Hispanic Citizen by birth or naturalization
3 93823  58      2     Other Hispanic Citizen by birth or naturalization
4 93830  67      1 non-Hispanic black Citizen by birth or naturalization
5 93840  47      1 non-Hispanic Asian Citizen by birth or naturalization
                  Education Marital_status Household_size    Annual_income
1 Some college or AA degree  Never married              5 $100000 and Over
2 College graduate or above        Married              2 $100000 and Over
3 College graduate or above        Married              2     Under $20000
4 Some college or AA degree        Married              2 $75000 to $99999
5 College graduate or above        Married              3 $100000 and Over
  Sedentary_min Height Weight Health_insurance Private_insurance Cancer
1           360     73    202              Yes               Yes     No
2           600     71    175              Yes              <NA>     No
3           180     64    170               No              <NA>     No
4           360     75    252              Yes              <NA>     No
5           600     70    172              Yes               Yes     No
  Heart_attack Stroke Inc_exercise Dec_salt Dec_fat LDL_Cholesterol
1           No     No           No       No     Yes              97
2           No     No           No      Yes     Yes              94
3           No     No          Yes       No     Yes             156
4           No    Yes          Yes      Yes     Yes              81
5           No     No          Yes       No      No             104
  Triglyceride
1           78
2          136
3          128
4           55
5           52

To keep the duplicates, we can use UNION ALL. The result outputs 6 rows in total.

SELECT *
FROM nhanes_set1
UNION ALL 
SELECT * 
FROM nhanes_set2
6 records
SEQN Age Gender Ethnicity Citizenship Education Marital_status Household_size Annual_income Sedentary_min Height Weight Health_insurance Private_insurance Cancer Heart_attack Stroke Inc_exercise Dec_salt Dec_fat LDL_Cholesterol Triglyceride
93731 20 1 Mexican American Citizen by birth or naturalization Some college or AA degree Never married 5 $100000 and Over 360 73 202 Yes Yes No No No No No Yes 97 78
93801 59 1 Other Hispanic Citizen by birth or naturalization College graduate or above Married 2 $100000 and Over 600 71 175 Yes NA No No No No Yes Yes 94 136
93823 58 2 Other Hispanic Citizen by birth or naturalization College graduate or above Married 2 Under $20000 180 64 170 No NA No No No Yes No Yes 156 128
93823 58 2 Other Hispanic Citizen by birth or naturalization College graduate or above Married 2 Under $20000 180 64 170 No NA No No No Yes No Yes 156 128
93830 67 1 non-Hispanic black Citizen by birth or naturalization Some college or AA degree Married 2 $75000 to $99999 360 75 252 Yes NA No No Yes Yes Yes Yes 81 55
93840 47 1 non-Hispanic Asian Citizen by birth or naturalization College graduate or above Married 3 $100000 and Over 600 70 172 Yes Yes No No No Yes No No 104 52 
bind_rows(nhanes_set1, nhanes_set2)
   SEQN Age Gender          Ethnicity                        Citizenship
1 93731  20      1   Mexican American Citizen by birth or naturalization
2 93801  59      1     Other Hispanic Citizen by birth or naturalization
3 93823  58      2     Other Hispanic Citizen by birth or naturalization
4 93823  58      2     Other Hispanic Citizen by birth or naturalization
5 93830  67      1 non-Hispanic black Citizen by birth or naturalization
6 93840  47      1 non-Hispanic Asian Citizen by birth or naturalization
                  Education Marital_status Household_size    Annual_income
1 Some college or AA degree  Never married              5 $100000 and Over
2 College graduate or above        Married              2 $100000 and Over
3 College graduate or above        Married              2     Under $20000
4 College graduate or above        Married              2     Under $20000
5 Some college or AA degree        Married              2 $75000 to $99999
6 College graduate or above        Married              3 $100000 and Over
  Sedentary_min Height Weight Health_insurance Private_insurance Cancer
1           360     73    202              Yes               Yes     No
2           600     71    175              Yes              <NA>     No
3           180     64    170               No              <NA>     No
4           180     64    170               No              <NA>     No
5           360     75    252              Yes              <NA>     No
6           600     70    172              Yes               Yes     No
  Heart_attack Stroke Inc_exercise Dec_salt Dec_fat LDL_Cholesterol
1           No     No           No       No     Yes              97
2           No     No           No      Yes     Yes              94
3           No     No          Yes       No     Yes             156
4           No     No          Yes       No     Yes             156
5           No    Yes          Yes      Yes     Yes              81
6           No     No          Yes       No      No             104
  Triglyceride
1           78
2          136
3          128
4          128
5           55
6           52

INTERSECTION

INTERSECT returns the rows that appear in both sets. Using the same nhanes_set1 and nhanes_set2, the result only outputs the row corresponding to participant 93823.

SELECT *
FROM nhanes_set1
INTERSECT 
SELECT * 
FROM nhanes_set2
1 records
SEQN Age Gender Ethnicity Citizenship Education Marital_status Household_size Annual_income Sedentary_min Height Weight Health_insurance Private_insurance Cancer Heart_attack Stroke Inc_exercise Dec_salt Dec_fat LDL_Cholesterol Triglyceride
93823 58 2 Other Hispanic Citizen by birth or naturalization College graduate or above Married 2 Under $20000 180 64 170 No NA No No No Yes No Yes 156 128 
intersect(nhanes_set1, nhanes_set2)
   SEQN Age Gender       Ethnicity                        Citizenship
1 93823  58      2  Other Hispanic Citizen by birth or naturalization
                  Education Marital_status Household_size Annual_income
1 College graduate or above        Married              2  Under $20000
  Sedentary_min Height Weight Health_insurance Private_insurance Cancer
1           180     64    170               No              <NA>     No
  Heart_attack Stroke Inc_exercise Dec_salt Dec_fat LDL_Cholesterol
1           No     No          Yes       No     Yes             156
  Triglyceride
1          128

EXCEPT

EXCEPT returns rows from the first set (the one on the FROM query) that do not appear in the second.

SELECT *
FROM nhanes_set1
EXCEPT 
SELECT * 
FROM nhanes_set2
2 records
SEQN Age Gender Ethnicity Citizenship Education Marital_status Household_size Annual_income Sedentary_min Height Weight Health_insurance Private_insurance Cancer Heart_attack Stroke Inc_exercise Dec_salt Dec_fat LDL_Cholesterol Triglyceride
93801 59 1 Other Hispanic Citizen by birth or naturalization College graduate or above Married 2 $100000 and Over 600 71 175 Yes NA No No No No Yes Yes 94 136
93731 20 1 Mexican American Citizen by birth or naturalization Some college or AA degree Never married 5 $100000 and Over 360 73 202 Yes Yes No No No No No Yes 97 78 
setdiff(nhanes_set1, nhanes_set2)
   SEQN Age Gender        Ethnicity                        Citizenship
1 93731  20      1 Mexican American Citizen by birth or naturalization
2 93801  59      1   Other Hispanic Citizen by birth or naturalization
                  Education Marital_status Household_size    Annual_income
1 Some college or AA degree  Never married              5 $100000 and Over
2 College graduate or above        Married              2 $100000 and Over
  Sedentary_min Height Weight Health_insurance Private_insurance Cancer
1           360     73    202              Yes               Yes     No
2           600     71    175              Yes              <NA>     No
  Heart_attack Stroke Inc_exercise Dec_salt Dec_fat LDL_Cholesterol
1           No     No           No       No     Yes              97
2           No     No           No      Yes     Yes              94
  Triglyceride
1           78
2          136

Note that EXCEPT removes all occurrences of duplicate data from the first set.

If you wish to removes one occurrence of duplicate data from the first set for every occurrence in the second set, use EXCEPT ALL.

Merging datasets on column level

We can merge datasets using JOIN if they share common columns. The general syntax is A JOIN_TYPE B, where JOIN_TYPE depends on the join operation we want to perform. This syntax would perform the join based on all shared attributes (columns) between A and B. If the shared attributes you wish to merge on are named differently in A and B, we can use ON to specify the condition. For instance, A JOIN_TYPE B ON A.colname1 = B.colname2 will join rows from A and B by matching values between colname1 from A and colname2 from B.

In this section, we will use the following datasets.

nhanes_demographics includes ID, Age, Gender and Education level of 5 patients.

SELECT *
FROM nhanes_demographics
5 records
SEQN Age Gender Education
93731 20 1 Some college or AA degree
93840 47 1 College graduate or above
93920 61 2 College graduate or above
93972 35 1 Some college or AA degree
94069 61 1 High school graduate/GED or equivalent

nhanes_health includes ID, Heart attack status, and Stroke Status of 6 patients.

SELECT *
FROM nhanes_health
6 records
SEQN Heart_attack Stroke
93731 No No
93840 No No
93920 No No
102838 No No
102880 No No
102947 No No

Note that ID 93731, 93840, and 93920 appear on both table. ID 93972 and 94069 only appear in nhanes_demographics and ID 102838, 102880, and 102947 only appear in nhanes_health.

Suppose we are only interested in patients’ age and their stroke status, there are 4 main types of joins:

Inner Join

An inner natural join:

SELECT SEQN, Age, Stroke
FROM nhanes_demographics
NATURAL JOIN nhanes_health
3 records
SEQN Age Stroke
93731 20 No
93840 47 No
93920 61 No 
nhanes_demographics %>%
  inner_join(nhanes_health, by = "SEQN") %>%
  select(SEQN, Age, Stroke)
   SEQN Age Stroke
1 93731  20     No
2 93840  47     No
3 93920  61     No

will include only IDs that are in the intersection of nhanes_demographics and nhanes_health.

Outer Join

A full outer join:

SELECT SEQN, Age, Stroke
FROM nhanes_demographics
NATURAL FULL JOIN nhanes_health
8 records
SEQN Age Stroke
93731 20 No
93840 47 No
93920 61 No
102838 NA No
102880 NA No
102947 NA No
94069 61 NA
93972 35 NA 
nhanes_demographics %>%
  full_join(nhanes_health, by = "SEQN") %>%
  select(SEQN, Age, Stroke)
    SEQN Age Stroke
1  93731  20     No
2  93840  47     No
3  93920  61     No
4  93972  35   <NA>
5  94069  61   <NA>
6 102838  NA     No
7 102880  NA     No
8 102947  NA     No

will include all IDs that are in either nhanes_demographics or nhanes_health. For patients that appear in nhanes_demographics, but not in nhanes_health (i.e., 93972 and 94069), null values will be inserted to Stroke (column in nhanes_health). Similarly, For patients that appear in nhanes_health, but not in nhanes_demographics (i.e., 102838, 102880, and 102947), null values will be inserted to Age (column in nhanes_demographics).

Left outer join

A left outer join:

SELECT SEQN, Age, Stroke
FROM nhanes_demographics
NATURAL LEFT JOIN nhanes_health
5 records
SEQN Age Stroke
93731 20 No
93840 47 No
93920 61 No
94069 61 NA
93972 35 NA 
nhanes_demographics %>%
  left_join(nhanes_health, by = "SEQN") %>%
  select(SEQN, Age, Stroke)
   SEQN Age Stroke
1 93731  20     No
2 93840  47     No
3 93920  61     No
4 93972  35   <NA>
5 94069  61   <NA>

will include all IDs that are in the intersection (i.e., 93731, 93840, and 93920) plus those that are in nhanes_demographics only (i.e., 93972 and 94069) with null values inserted to Stroke (column in nhanes_health).

Right outer join

A right outer join:

SELECT SEQN, Age, Stroke
FROM nhanes_demographics
NATURAL RIGHT JOIN nhanes_health
6 records
SEQN Age Stroke
93731 20 No
93840 47 No
93920 61 No
102838 NA No
102880 NA No
102947 NA No 
nhanes_demographics %>%
  right_join(nhanes_health, by = "SEQN")%>%
  select(SEQN, Age, Stroke)
    SEQN Age Stroke
1  93731  20     No
2  93840  47     No
3  93920  61     No
4 102838  NA     No
5 102880  NA     No
6 102947  NA     No

will include all IDs that are in the intersection (i.e., 93731, 93840, and 93920) plus those that are in nhanes_health only (i.e., 102838, 102880, and 102947) with null values inserted to Age (column in nhanes_demographics).

Return to Learning Hub Homepage

Learning Hub Homepage