Query to postgresql give ERROR: relation "dual" does not exist;-CodePudding

I have been trying to figure this out for a while now. But why does it return ERROR: relation "dual" does not exist; I thought DUAL was just supposed to be a dummy table?

SELECT
(SELECT name,w.element from sources s inner join weather w on w.source_id=s.source_id) AS table_a,
(SELECT DISTINCT time from weather) AS table_b 
FROM DUAL;

CodePudding user response：

it's not very clear what you're trying to obtain but you can try

SELECT w.time name,w.element from sources s inner join weather w on w.source_id=s.source_id

CodePudding user response：

You are learning SQL and want to experiment with subqueries. I hope my answer can shed some light on this topic.

With SQL we select from tables, and the result again is a table. That is a data set that consists of columns and rows. As our query result is a table, we can select from it, and the original query becomes a subquery.

One example is this:

SELECT *
FROM (SELECT DISTINCT time FROM weather) t
CROSS JOIN (SELECT DISTINCT element FROM weather) e
LEFT OUTER JOIN weather w ON w.time = t.time AND w.element = e.element
ORDER BY t.time, e.element
;

This gives us all combinations of time and element from the weather table and in case a row exists for a particular time and element pair we show its data. In order to achieve this we select the distinct times from the weather table and make this our first subquery (also known as "derived table", because we derive a new table from an existing one and select from it). We do the same with the elements from the table and then join the two results, just as we do with tables that are stored in the database. At last we outer join the weather table to the result from the two derived tables.

We use subqueries also with [NOT] EXISTS and [NOT] IN. For instance:

SELECT *
FROM sources
WHERE source_id IN (SELECT source_id FROM weather);

Here we only select the sources that have at least one entry in the weather table. This is different from a join, as a join would result in the sources appearing multiple times in the result, but we want them just once.

At last, subqueries can also be used in place of an expression. An expression results in a single value, e.g.

SELECT
  time   INTERVAL '2 HOURS' AS two_hours_later,
  element,
  'unknown' AS status
FROM weather
WHERE time < CURRENT_DATE;

Here we have many expressions. time is a time we get from the table row, INTERVAL '2 HOURS' is an interval of two hours, time INTERVAL '2 HOURS' is an expression that results from adding the time with the interval, element is an expression, again a value from the table, 'unknown' is one, time and CURRENT_DATE are expressions, and the result of their comparison time < CURRENT_DATE is again an expression. At all these places we can put subqueries, but as expressions represent a single value, the subquery must also result in a single value, i.e. one row and one column. (Zero rows are also accepted and the result is considered NULL then.)

SELECT
  source_id,
  (
    SELECT MAX(time)
    FROM weather
    WHERE weather.source_id ) sources.source_id
  ) AS max_time
FROM sources
WHERE source_id > (SELECT MIN(source_id) FROM sources);

Here we look at all rows except for the ones with the minimum source ID. We get the minimum source ID with a subquery that results in that one value. For each row we select the source_id and the maximum time we find for it in the weather table. For this maximum time we use a subquery again. This time it is a correlated subquery, i.e. it relates to the main query (the sources table) in order to get the maximum time for the row's source ID.

You can select a single row by omitting the FROM clause:

SELECT 1 AS one, 2 AS two;

With subqueries again:

SELECT
  (SELECT MIN(time) FROM weather) AS min_time,
  (SELECT MAX(time) FROM weather) AS max_time
;

Here is your query:

SELECT
  (
    SELECT s.name, w.element 
    FROM sources s 
    INNER JOIN weather w ON w.source_id = s.source_id
  ) AS table_a,
  (SELECT DISTINCT time from weather) AS table_b 
;

Your subqueries are in the select clause, so they too are expressions, but they don't result in a single value. Both result in multiple rows and one even results in multiple columns. This is why you are getting a syntax error.