Can someone help me undertsand the IN(...) AS... in the select. This is the first time I'm seeing something like this. Also, how does the database determine the row value or newID? This is on Postgres datababse.
SELECT tbl_a.*, (
tbl_z.z_id IN (
SELECT x_id
FROM table_x
WHERE name = '...'
)
) AS newID
FROM (...)
newId will be of type boolean. true, if
tbl_z.z_id IN (
SELECT x_id
FROM table_x
WHERE name = '...'
)
and false if not.
AS column_name is alias.
update answering your comment.
https://www.postgresql.org/docs/current/static/sql-select.html#SQL-WHERE
where condition is any expression that evaluates to a result of type boolean.
So if you have IN (...) in WHERE it returns same boolean as in column list. True or false. No difference - where or as column, returns either true or false
The second column is the answer to the question "is tbl_z.z_id in the list of x_id from table_x where name = '...'?"
As that's a yes/no question (which 'IN' always means), it's a boolean (either true or false).
The "AS" clause tells it what to name that column (it's not directly from a table, so it doesn't know what to call it). It's not strictly required, but it makes addressing that column in your output much easier, you can also use it if you want the column name to be different then the name in the table you're coming from (if you're joining two tables, and they happen to have each have a column with the same names is a use case for this).
