SQL Joins & Advanced Queries
INNER JOIN, LEFT/RIGHT JOIN, CROSS JOIN, CTEs, window functions, nested queries, optimization
1Which type of JOIN returns only the rows that have a match in both tables?
Which type of JOIN returns only the rows that have a match in both tables?
Answer
INNER JOIN returns only the rows that have a match in both tables. If a row from the left table has no match in the right table (or vice versa), it is excluded from the result. This is the most restrictive and most commonly used type of JOIN for combining related data.
2What is the result of a LEFT JOIN when the right table has no match?
What is the result of a LEFT JOIN when the right table has no match?
Answer
LEFT JOIN keeps all rows from the left table, even if no match exists in the right table. In that case, the columns from the right table are filled with NULL values. This preserves the integrity of the main table's data while adding optional information.
3How to find customers who have not placed any orders using a JOIN?
How to find customers who have not placed any orders using a JOIN?
Answer
To find records without a match, use a LEFT JOIN followed by a WHERE filter on a right table column IS NULL. This technique is more performant than a NOT IN subquery because it avoids scanning the orders table multiple times. This is a classic pattern for detecting orphan data.
What does a CROSS JOIN produce as a result?
What is a SELF JOIN and when to use it?
+19 interview questions
Other Data Science & ML interview topics
Python Basics
Python Object-Oriented Programming
Python Data Structures
Git Fundamentals
SQL Basics
NumPy Fundamentals
Pandas Basics
Jupyter & Google Colab
Advanced Pandas
Visualization with Matplotlib & Seaborn
Interactive Visualizations with Plotly
Descriptive Statistics
Inferential Statistics
Web Scraping
BigQuery & Cloud Data
Feature Engineering
Supervised ML: Regression
Supervised ML: Classification
Decision Trees & Ensembles
Unsupervised ML
ML Pipelines & Validation
Time Series & Forecasting
Deep Learning Fundamentals
TensorFlow & Keras
CNN & Image Classification
RNN & Sequences
Transformers & Attention
NLP & Hugging Face
GenAI & LangChain
MLOps & Deployment
Master Data Science & ML for your next interview
Access all questions, flashcards, technical tests, code review exercises and interview simulators.
Start for free