I have two tables which are unrelated to each other. I want to do a count on one column in one table and max of one column in another table. I have written a query like this:
select a.mycount, b.mymax from (select count(col1) as mycount from Table1 where <cond1>) a, (select max(col2) as mymax from Table2 where <cond2>) b
Is there any better way of doing this?
There are at least two more, but they are only trivially different, and not necessarily better. Yours, by the way, is a perfectly acceptable cross join.
The first method is to use a UNION to produce two summary rows, with an extra column to identify where the aggregate number came from.
select 'Table1' as source , count(col1) as mynumber from Table1 where <cond1> union all select 'Table2' as source , max(col2) as mynumber from Table2 where <cond2>
The second is to use subselects in a SELECT from no table.
select (select count(col1) from Table1 where <cond1>) as mycount , (select max(col2) as mymax from Table2 where <cond2>) as mymax
Note that not every database system will allow you to run a SELECT without a FROM clause.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.