PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – As Statement in SQL

while studying about the As statment i get to knwon it is executed at the end of the uqery so if in case i am trying to use the alisd name i have created in a query like

select film as movie from films where movie = "3 Idiots"

then it is giving me error and thats expected as "AS" statment is just execeuted at the time of ouput but if the same thing i do for a sub query for an example

select title, 
       first_name, 
       last_name 
from  film 
INNER JOIN  ( select first_name,
                     last_name,
                     film_id 
                     from actor 
                     INNEr JOIN film_actor ON actor.actor_id = film_actor.actor_id
            ) As actor_info ON film.film_id = actor_info.film_id
Where actor_info.first_name = 'Nick' 
AND`walker` actor_info.last_name = 'Wahlberg';

then it is working fine , so can anyone help me understand this concept

Tags:

2

Answers


  1. 0

    When using SQL, the AS keyword is typically used to alias columns or tables, but not to alias individual records. In your query, it looks like you’re trying to use AS to alias a movie title, which is not a valid use of the keyword. Here’s how you can select a film titled "3 Idiots" without using AS:

    SELECT *
    FROM (
    SELECT movie
    FROM films
    ) AS movie_list
    WHERE movie = ‘3 Idiots’;

    In this query, the inner query selects the movie column from the films table, and then aliases the result as "movie_list". The outer query selects all columns from the aliased subquery where the movie title is "3 Idiots".

    Login or Signup to reply.
  2. 0

    The reason is that the WHERE clause evaluates before the SELECT clause and hence the aliases are not available to be used in the WHERE clause of the same query. So, you cannot use movie in the WHERE clause like in below example:

    SELECT film AS movie
FROM films 
    --WHERE movie = "3 Idiots" --movie is not a valid column name
WHERE film = "3 Idiots"; --film is a valid column name

    But the subquery is processed before the outer query, so it’s available to be used in the WHERE clause.

    SELECT title, 
       first_name, 
       last_name 
FROM film 
INNER JOIN ( 
            SELECT first_name,
                   last_name,
                   film_id 
              FROM actor 
              INNER JOIN film_actor 
                ON actor.actor_id = film_actor.actor_id
           ) AS actor_info 
  ON film.film_id = actor_info.film_id
WHERE actor_info.first_name = 'Nick' --actor_info is valid because it's been processed in the inner query
  AND actor_info.last_name = 'Wahlberg';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search