PostgreSQL single query SELECT single row FROM two tables without JOIN

Question

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.

Joe Love · Accepted Answer · 2014-12-05 20:55:26Z

31

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;

answered Dec 5, 2014 at 20:55

Joe Love

6,1262 gold badges24 silver badges33 bronze badges

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

2 Comments

Moses Liao GZ Over a year ago

how do you do this in Ruby on Rails?

Joe Love Over a year ago

I'm no ROR expert, but it's basically querying from 2 tables without joining them. Do you have an example of a "select * from blah" type query in ROR?

Denis de Bernardy · Accepted Answer · 2014-12-05 20:56:04Z

8

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

answered Dec 5, 2014 at 20:56

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

2 Comments

Joe Love Over a year ago

same answer as I posted.

Denis de Bernardy Over a year ago

@JoeLove: Answered at very much the same time. If you're the one who down voted, you might want to check this out for future reference.

Collectives™ on Stack Overflow

PostgreSQL single query SELECT single row FROM two tables without JOIN

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related