Selecting from two non-related tables at the same time

Suppose I have a table with lots of rows and columns (alias: bigtable), and a table that always has 1 row, but multiple columns (alias: 1rowtable). The 1rowtable has nothing to do with bigtable, it is just there for some settings my script uses that are modified dynamically. So I cannot use static SQLCMD variables for that and I cannot use normal variables for that either because my script has GO statements.

Now I want to write a select statement that accesses BOTH tables.

If I do:

SELECT ... FROM bigtable, 1rowtable

​x
 
SELECT ... FROM bigtable, 1rowtable​

it does a CROSS JOIN so that is bad, can’t go that route.

If I use a CTE for 1rowtable, I have to access its fields with

SELECT field FROM 1rowtable

 
SELECT field FROM 1rowtable​

So that is bad too. Same with a table valued function like:

CREATE FUNCTION getSetting(@name nvarchar(40))
RETURNS TABLE
AS
RETURN (SELECT name FROM 1rowtable WHERE name = @name)

 
CREATE FUNCTION getSetting(@name nvarchar(40))RETURNS TABLEASRETURN (SELECT name FROM 1rowtable WHERE name = @name)​

Obviously I cannot use a scalar function at all because it only returns a specific datatype, but the settings have different datatypes. Yet, obviously I would like to use it LIKE a scalar function of course without doing the ‘SELECT .. FROM dbo.getfieldfrom1rowtable(..)’ stuff, since I am using the 1rowtable rather often in queries.

I also tried doing:

SELECT
  (SELECT
     <expression involving bigtable and 1rowtable>,
     <expression involving bigtable and 1rowtable>,
     <expression involving bigtable and 1rowtable>,
     ...
  FROM 1rowtable)
FROM bigtable

 
SELECT  (SELECT     <expression involving bigtable and 1rowtable>,     <expression involving bigtable and 1rowtable>,     <expression involving bigtable and 1rowtable>,     ...  FROM 1rowtable)FROM bigtable​

But of course a subselect cannot select more than one item if it does not begin with exists…

So what should I do? It seems I will have to continue using ‘SELECT .. FROM dbo.getfieldfrom1rowtable(..)’ every time? Just curious 🙂

PS. ms sql server 2008r2

Answer

There is nothing wrong with using a cross join to bring together rows from tables, particularly when one only has one row.

Use the syntax:

select bt.*, ort.*
from bigtable bt cross join
     onerowtable ort

 
select bt.*, ort.*from bigtable bt cross join     onerowtable ort​

There is nothing inherently “wrong” with cross joins, when they are used correctly. The problem is when they are used inadvertently. If you cross join two tables with a million rows . . . well, your temp space is going to fill up, your processor(s) will be very busy, and the query will eventually crash due to a lack of resources.

However, cross joining a table with one row to another table poses no problems at all.

Advertisement

Answer