The Problem
Consider the following tables: Person and Address.
Table: Person
| Column Name | Type |
|---|---|
| personId | int |
| lastName | varchar |
| firstName | varchar |
personId is the primary key column for this table. This table contains information about the ID of some persons and their first and last names.
Table: Address
| Column Name | Type |
|---|---|
| addressId | int |
| personId | int |
| city | varchar |
| state | varchar |
addressId is the primary key column for this table. Each row of this table contains information about the city and state of one person with ID = personId.
The task is to write an SQL query to report the first name, last name, city, and state of each person in the Person table. If the address of a personId is not present in the Address table, report null instead.
The result table can be returned in any order.
The Solution
To solve this problem, we can use a LEFT JOIN SQL operation. The LEFT JOIN keyword returns all records from the left table (Person), and the matched records from the right table (Address). The result is NULL from the right side, if there is no match.
This is especially effective in this case because we want to report all people regardless of whether their address is available or not. This makes our solution cost-effective in terms of performance, as we don't have to perform separate queries or complex subqueries.
The Code
Here's the MSSQL query that achieves this:
SELECT p.firstName, p.lastName, a.city, a.state
FROM Person p LEFT JOIN Address a ON p.personId = a.personId
This query selects firstName, lastName from the Person table and city, state from the Address table. The LEFT JOIN ensures that every personId from the Person table appears in the result, regardless of whether it appears in the Address table or not.
In the case where a personId from the Person table doesn't have a matching entry in the Address table, the city and state fields are reported as NULL.
The result will be a comprehensive list of all persons along with their respective addresses (if available).
For more insightful solutions and tech-related content, feel free to connect with me on my Beacons page.
ranggakd - Link in Bio & Creator Tools | Beacons
@ranggakd | center details summary summary Oh hello there I m a an Programmer AI Tech Writer Data Practitioner Statistics Math Addict Open Source Contributor Quantum Computing Enthusiast details center.
beacons.ai
Top comments (0)