CODEWITHSUNDEEP
postgresql
user3750809
user3750809

Reputation:

PostgreSQL single query SELECT single row FROM two tables without JOIN

The result of a query I need to create will only return a single row. The data I need to pull comes from two tables without any relational columns. The second table only has additional rows added once per year so I'll construct the query via PHP as necessary.

I know how to use SQL sub-SELECTs however I'm not sure how to SELECT multiple columns FROM a second table when there is no relational data in a performance oriented/dynamic way.

Here is a static example where I use multiple sub-SELECTs to visualize what I'm trying to do...

SELECT t1.a, 
t1.b, 
t1.c, 
(SELECT t2.d FROM table2) AS d, 
(SELECT t2.e FROM table2) AS e, 
(SELECT t2.f FROM table2) AS f, 
(SELECT t2.g FROM table2) AS g, 
t1.h, 
t1.i
FROM table1 AS t1;

How do I dynamically and efficiently pull multiple columns from a second table that has no relational columns with the first table?

I do not want to create a second separate query as it would be cheap solution, most likely have some impact on performance and worst of all I wouldn't expand my understanding of SQL.

Upvotes: 22

Views: 37995

Answers (2)

Denis de Bernardy
Denis de Bernardy

Reputation: 78473

The syntax to do so is:

SELECT t1.a, 
       t1.b, 
       t1.c, 
       t2.d, 
       t2.e, 
       t2.f, 
       t2.g, 
       t1.h, 
       t1.i
FROM table1 AS t1, table2 as t2;

If you're not joining the two tables though, you'll usually want two queries. Else, you'll get a cartesian product of the two tables:

http://en.wikipedia.org/wiki/Cartesian_product

Upvotes: 8

Joe Love
Joe Love

Reputation: 5962

Sounds like you need a cartesian join (no join) -- but you WILL multiply the values together (ie, if table 1 has 100 rows and table 2 has 10 rows, you'll return 1000 rows)

SELECT t1.a, 
t1.b, 
t1.c, 
t2.d, 
t2.e, 
t2.f,
t2.g,
t1.h, 
t1.i
FROM table1 AS t1, table2 as t2;

Upvotes: 31

Related Questions