SQL BigQuery Questions+Answers file:///Users/kddv/Library/CloudStorage/GoogleDrive-fdompreh@wisc....
SQL BigQuery Questions+Answers
Kwaku
2025-05-28
Here is a detailed explanation of LEFT JOIN and other types of SQL joins, along with practical questions and
examples involving BigQuery. The content is formatted in R Markdown for easy use.
# SQL Joins and BigQuery Examples
Overview of SQL Joins
Joins are used to combine rows from two or more tables based on a related column. Here are the most
common types of joins:
1. INNER JOIN: Returns rows where there is a match in both tables.
2. LEFT JOIN: Returns all rows from the left table and matching rows from the right table. If no match is
found, NULL values are returned for the right table.
3. RIGHT JOIN: Returns all rows from the right table and matching rows from the left table. If no match is
found, NULL values are returned for the left table.
4. FULL OUTER JOIN: Returns all rows when there is a match in either table. If no match is found, NULL
values are returned for the missing side.
5. CROSS JOIN: Returns the Cartesian product of two tables (all combinations of rows).
High-Level Questions
1. Conceptual Questions
• What is the difference between INNER JOIN and LEFT JOIN ? When would you use each?
• How does a FULL OUTER JOIN differ from a LEFT JOIN and RIGHT JOIN ?
• What are the performance implications of using joins on large datasets in BigQuery?
• How can you optimize a query that uses multiple joins in BigQuery?
2. Practical Scenarios
• How would you use a LEFT JOIN to find all brands that do not have any associated models?
• How can you use a FULL OUTER JOIN to compare two tables and find mismatched rows?
• How would you use a CROSS JOIN to generate all possible combinations of two columns?
Low-Level Questions
1 of 5 5/28/25, 02:48
SQL BigQuery Questions+Answers file:///Users/kddv/Library/CloudStorage/GoogleDrive-fdompreh@wisc....
1. Basic Join Queries
Q: Write a query to fetch all phone brands and their associated models using an
INNER JOIN .
Answer:
SELECT pb.phone_brand_name, pm.phone_model_name
FROM phone_brand pb
INNER JOIN phone_model pm ON pb.phone_brand_id = pm.phone_brand_id;
Q: Write a query to fetch all phone brands, including those without any models,
using a LEFT JOIN .
Answer:
SELECT pb.phone_brand_name, pm.phone_model_name
FROM phone_brand pb
LEFT JOIN phone_model pm ON pb.phone_brand_id = pm.phone_brand_id;
Q: Write a query to fetch all phone models, including those without a matching
brand, using a RIGHT JOIN .
Answer:
SELECT pb.phone_brand_name, pm.phone_model_name
FROM phone_brand pb
RIGHT JOIN phone_model pm ON pb.phone_brand_id = pm.phone_brand_id;
2. Advanced Join Queries
Q: Write a query to find all brands that do not have any associated models.
Answer:
SELECT pb.phone_brand_name
FROM phone_brand pb
LEFT JOIN phone_model pm ON pb.phone_brand_id = pm.phone_brand_id
WHERE pm.phone_model_id IS NULL;
Q: Write a query to compare two tables ( phone_brand and phone_model ) and find
mismatched rows using a FULL OUTER JOIN .
Answer:
2 of 5 5/28/25, 02:48
SQL BigQuery Questions+Answers file:///Users/kddv/Library/CloudStorage/GoogleDrive-fdompreh@wisc....
SELECT pb.phone_brand_name, pm.phone_model_name
FROM phone_brand pb
FULL OUTER JOIN phone_model pm ON pb.phone_brand_id = pm.phone_brand_id
WHERE pb.phone_brand_id IS NULL OR pm.phone_model_id IS NULL;
Q: Write a query to generate all possible combinations of phone brands and
conditions ( New , Used ) using a CROSS JOIN .
Answer:
SELECT pb.phone_brand_name, pc.condition
FROM phone_brand pb
CROSS JOIN (SELECT 'New' AS condition UNION ALL SELECT 'Used' AS condition) pc;
BigQuery-Specific Examples
1. Optimizing Joins in BigQuery
Q: How can you optimize a query that joins large tables in BigQuery?
Answer: - Use partitioning and clustering to reduce the amount of data scanned. - Filter data before
performing the join using WHERE clauses. - Use EXPLAIN to analyze query performance.
Example:
SELECT pb.phone_brand_name, pm.phone_model_name
FROM `project.dataset.phone_brand` pb
LEFT JOIN `project.dataset.phone_model` pm
ON pb.phone_brand_id = pm.phone_brand_id
WHERE pb.region = 'US';
2. Handling NULL Values in Joins
Q: Write a query to replace NULL values in a LEFT JOIN result with a default value.
Answer:
SELECT pb.phone_brand_name,
IFNULL(pm.phone_model_name, 'No Model') AS phone_model_name
FROM `project.dataset.phone_brand` pb
LEFT JOIN `project.dataset.phone_model` pm
ON pb.phone_brand_id = pm.phone_brand_id;
3 of 5 5/28/25, 02:48
SQL BigQuery Questions+Answers file:///Users/kddv/Library/CloudStorage/GoogleDrive-fdompreh@wisc....
3. Using ARRAY_AGG with Joins
Q: Write a query to fetch all models for each brand as an array.
Answer:
SELECT pb.phone_brand_name,
ARRAY_AGG(pm.phone_model_name) AS models
FROM `project.dataset.phone_brand` pb
LEFT JOIN `project.dataset.phone_model` pm
ON pb.phone_brand_id = pm.phone_brand_id
GROUP BY pb.phone_brand_name;
4. Self-Joins in BigQuery
Q: Write a query to find duplicate phone brands based on their name.
Answer:
SELECT a.phone_brand_name, COUNT(*)
FROM `project.dataset.phone_brand` a
JOIN `project.dataset.phone_brand` b
ON a.phone_brand_name = b.phone_brand_name
WHERE a.phone_brand_id != b.phone_brand_id
GROUP BY a.phone_brand_name
HAVING COUNT(*) > 1;
Practical Exercises
1. Query Writing
Q: Write a query to fetch all attributes for a specific sub-segment ID ( sub_seg_id )
and their associated options using a LEFT JOIN .
Answer:
SELECT a.attribute_name, a.val_options
FROM `project.dataset.attributes` a
LEFT JOIN `project.dataset.sub_segments` ss
ON a.sub_seg_id = ss.sub_seg_id
WHERE a.sub_seg_id = 8;
Q: Write a query to count how many attributes are marked as is_required = 1
for each sub-segment.
Answer:
4 of 5 5/28/25, 02:48
SQL BigQuery Questions+Answers file:///Users/kddv/Library/CloudStorage/GoogleDrive-fdompreh@wisc....
SELECT sub_seg_id, COUNT(*) AS required_count
FROM `project.dataset.attributes`
WHERE is_required = 1
GROUP BY sub_seg_id;
2. Data Validation
Q: Write a query to ensure that all attribute_slug values in the attributes
table are unique.
Answer:
SELECT attribute_slug, COUNT(*)
FROM `project.dataset.attributes`
GROUP BY attribute_slug
HAVING COUNT(*) > 1;
Q: Write a query to find all rows in the attributes table where val_options is
empty or null.
Answer:
SELECT *
FROM `project.dataset.attributes`
WHERE val_options IS NULL OR val_options = '';
Conclusion
These questions and examples are designed to deepen your understanding of SQL joins and their application
in BigQuery. Start with the basic join queries to build a strong foundation, and then move on to advanced and
BigQuery-specific examples for real-world scenarios. ```
How to Use This
1. Copy the R Markdown content into an .Rmd file.
2. Use it as a guide to practice SQL joins and BigQuery-specific features.
3. Focus on optimizing queries for large datasets in BigQuery.
Let me know if you’d like further clarification or additional examples! 😊
5 of 5 5/28/25, 02:48
