CODEWITHSUNDEEP
sqlsubquery
jojo
jojo

Reputation: 315

Is SubQuery in Select Statement is A Good Choice for This Issue?

Assume I have two tables which structure like below:

Table People:

---------------------------------------------------
| Pepole |ClothBrandId  | ShoeBrandId | HatBrandId|
---------------------------------------------------
| Jo     | 1            | 2           | 3         |
---------------------------------------------------

Table Brand:

 -------------------------
 | BrandId |BrandName    |
 -------------------------
 | 1       | NBrand      |
 -------------------------
 | 2       | ABrand      |
 -------------------------
 | 3       | PBrand      |
 -------------------------

What I want is use T-SQL to generate a set as below:

    ---------------------------------------------------------
    | Pepole |ClothBrandName  | ShoeBrandName | HatBrandName|
    ---------------------------------------------------------
    | Jo     | NBrand         | ABrand        | PBrand      |
    ---------------------------------------------------------

Currently, I think subquery in select statement can resolve this:

 SELECT
      People,
      SELECT BrandName as ClothBrandName FROM Brand WHERE BrandId=ClothBrandId,
      SELECT BrandName as ShoeBrandName FROM Brand WHERE BrandId=ShoeBrandId,
      SELECT BrandName as HatBrandName FROM Brand WHERE BrandId=HatBrandId
FROM 
People

But I don't think it a efficient solution. Another is use UNPIVOT then PIVOT. Which will be hard to read. Is there other appropriate ways for this？

Upvotes: 1

Views: 65

Answers (5)

Charles Bretana
Charles Bretana

Reputation: 146499

Three simple joins will work

Select p.People, 
   c.BrandName ClothBrandName,
   s.BrandName ShoeBrandName,
   h.BrandName HatBrandName
From People p
   Join Brand c On c.BrandId = p.ClothBrandId  
   Join Brand s On s.BrandId = p.ShoeBrandId 
   Join Brand h On h.BrandId = p.HatBrandId

but remember, the SQL Query Processor does not know from Joins vs Subqueries... The Parser (that reads your SQL and turns it into a cache plan) has to understand that, but inside the actual Query engine there are only three kinds of joins, and the query optimizer picks from these three based on the data statistics (and other factors). Whether you phrase your SQL as a join or as a correllated subquery does not control which of these three join types are used by the query engine. Quite often, if you do both versions, and they are logically equivalent, the show query plan will be identical.

  1. Nested Loops
  2. Hash Join
  3. Merge Join

Upvotes: 2

GarethD
GarethD

Reputation: 69759

I would go for three joins as opposed to subqueries:

 SELECT People,
        c.BrandName AS ClothBrandName,
        s.BrandName AS ShoeBrandName,
        h.BrandName AS HatBrandName
FROM    People AS p
        LEFT JOIN Brand AS c
            ON c.BrandID = p.ClothBrandId
        LEFT JOIN Brand AS s
            ON s.BrandID = p.ShoeBrandId
        LEFT JOIN Brand AS h
            ON h.BrandID = p.HatBrandId;

Example on SQL Fiddle

If your fields in People are not nullable then you can change your left joins to inner joins.

Upvotes: 1

Matthew
Matthew

Reputation: 25763

I would do 3 separate joins on Brand

SELECT 
    p.*, 
    cb.BrandName AS ClothBrandHane, 
    sb.BrandName AS ShoeBrandName, 
    hb.BrandName AS HatBrandName
FROM People p, 
JOIN Brand cb ON cb.BrandID = p.ClothBrandID
JOIN Brand sb ON sb.BrandID = p.ShoeBrandID
JOIN Brand hb ON hb.BrandID = p.HatBrandID

Upvotes: 1

Scott
Scott

Reputation: 3732

Here is one alternative - joining Table Brand three times:

SELECT People, CB.BrandName, SB.BrandName, HB.BrandName
FROM   People
  LEFT JOIN Brand AS CB on BrandId = ClothBrandId
  LEFT JOIN Brand AS SB on BrandId = ShoeBrandId
  LEFT JOIN Brand AS HB on BrandId = HatBrandId

Change this to JOIN only if you're absolutely sure all three brands will appear in the tables.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269703

The proper query would look like:

SELECT p.*,
       (SELECT BrandName FROM Brand b WHERE b.BrandId = p.ClothBrandId) as ClothBrandName,
       (SELECT BrandName FROM Brand b WHERE b.BrandId = p.ShoeBrandId) as ShoeBrandName,
       (SELECT BrandName FROM Brand b WHERE b.BrandId = p.HatBrandId) as HatBrandName
FROM People p;

I added table aliases. When working with more than one table, you should strive to use table aliases so someone reading the query (including yourself) knows where the columns are coming from.

This is as reasonable as doing left outer joins, which is really the other option. Note that this assumes that Brand(BrandId) is unique or a primary key. Otherwise, you might get multiple rows and an error in the query.

Upvotes: 1

Related Questions