Combining two SQL statements

Question

What would be the best way to combine the two SQL statements into one?

  SELECT MIN(capacity) AS mincapacity, MAX(capacity) AS maxcapacity 
  FROM room

  SELECT MIN(grade) AS mingrade, MAX(grade) AS maxgrade 
  FROM room_grade

Thanks in advance!

Why do you want to combine those those statements into one? At first glance it looks like a bad idea... — Mark Byers
– Mark Byers, Commented Mar 22, 2012 at 16:01
I need to extract the mincapacity, maxcapacity, mingrade and maxgrade values from the table. — CJS
– CJS, Commented Mar 22, 2012 at 16:01
You haven't actually given the schema for the tables nor what you want to achive but joining these seems illogical as your just getting the min and max of two columns. — Hansi
– Hansi, Commented Mar 22, 2012 at 16:02

user359040 · Accepted Answer · 2012-03-22 16:03:57Z

4

Given that both resultsets contain only one row each, the simplest way would be a cross join:

select * from
(SELECT MIN(capacity) AS mincapacity, MAX(capacity) AS maxcapacity FROM room) r
cross join
(SELECT MIN(grade) AS mingrade, MAX(grade) AS maxgrade FROM room_grade) g

answered Mar 22, 2012 at 16:03

user359040

Sign up to request clarification or add additional context in comments.

Comments

MatBailie · Accepted Answer · 2012-03-22 16:05:41Z

3

SELECT
  *
FROM
  (SELECT MIN(capacity) AS mincapacity, MAX(capacity) AS maxcapacity FROM room) AS room
CROSS JOIN
  (SELECT MIN(grade) AS mingrade, MAX(grade) AS maxgrade FROM room_grade) AS room_grade

The main benefits here are having all the records on one row, and not joining the component records of each table.

Answer that involve a join before the aggregation will introduce a significant overhead that will make a massive difference to performance.

answered Mar 22, 2012 at 16:05

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

3 Comments

sll Over a year ago

Wow, I thought that CROSS JOIN is a perfromance killer for such tasks, where is a trick, in aggregation function?

MatBailie Over a year ago

@sll - Aggregate, then join. So you are joining 1 record to 1 record. Rather than joining n records to m records, then aggregating.

Schalton Over a year ago

@MatBailie Can you take a look at stackoverflow.com/questions/39471531/…

Francis P · Accepted Answer · 2012-03-22 16:02:25Z

1

SELECT 'CAPACITY', MIN(capacity) AS min, MAX(capacity) AS max 
FROM room
UNION
SELECT 'GRADE', MIN(grade) AS min, MAX(grade) AS max 
FROM room_grade

should do the job.

answered Mar 22, 2012 at 16:02

Francis P

13.7k3 gold badges30 silver badges52 bronze badges

Comments

Taryn · Accepted Answer · 2012-03-22 16:02:25Z

1

you could do this with horrible JOIN syntax:

SELECT MIN(capacity) AS mincapacity, MAX(capacity) AS maxcapacity, MIN(grade) AS mingrade, MAX(grade) AS maxgrade
  FROM room, room_grade;

But you should use proper JOIN syntax

SELECT MIN(r.capacity) AS mincapacity, MAX(r.capacity) AS maxcapacity
    , MIN(rg.grade) AS mingrade, MAX(rg.grade) AS maxgrade
  FROM room r
  JOIN room_grade rg
    ON r.key = rg.key; (whatever the key is that joins the two tables)

answered Mar 22, 2012 at 16:02

Taryn

249k57 gold badges374 silver badges409 bronze badges

1 Comment

MatBailie Over a year ago

I wouldn't do this. The join adds an unecessary overhead, and will perform dramatically worse than the two separate queries. Also if records are present in one talbe but not the other this can change the results.

sll · Accepted Answer · 2012-03-22 16:02:46Z

1

EITHER

SELECT MIN(capacity) AS mincapacity, MAX(capacity) AS maxcapacity    FROM room
UNION ALL
SELECT MIN(grade) AS mingrade, MAX(grade) AS maxgrade    FROM room_grade

OR

SELECT MIN(r.capacity) AS mincapacity, MAX(r.capacity) AS maxcapacity, 
       MIN(rg.grade) AS mingrade, MAX(rg.grade) AS maxgrade    
FROM room r
INNER JOIN room_grade rg ON r.Id = rg.room_id

answered Mar 22, 2012 at 16:02

sll

62.7k22 gold badges109 silver badges157 bronze badges

4 Comments

MatBailie Over a year ago

The union is messy but fine, but the join will introduce a massive overhead. Also if records are present in one table but not the other this can change the results.

sll Over a year ago

Which JOIN overhead do you mean?

MatBailie Over a year ago

When specifying an INNER join, each record needs checking for existance in the other table. Keeping the queries separate yields (at worst) a full table scan of each table in the order that it currently exists. But with a join, at best, you'll need a merge join on the tables. This is much higher than niavely scanning each table independantly.

sll Over a year ago

Are you talking about MS Sql Server?

Brett Wait · Accepted Answer · 2012-03-22 16:03:48Z

1

You would want to join on an ID field. I assume the 'room' table has a unique key field, and the room_grade could have that same field. Do a join on the two something like so.

SELECT MIN(r.capacity) AS mincapacity, MAX(r.capacity) AS maxcapacity, MIN(g.grade) AS mingrade, MAX(g.grade) AS maxgrade
FROM room r
inner join room_grade g
on room r.roomId = g.roomId

answered Mar 22, 2012 at 16:03

Brett Wait

3973 silver badges7 bronze badges

1 Comment

MatBailie Over a year ago

The join will introduce a massive overhead. Also if records are present in one table but not the other this can change the results.

Collectives™ on Stack Overflow

Combining two SQL statements

6 Answers 6

Comments

3 Comments

Comments

1 Comment

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

3 Comments

Comments

1 Comment

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related