I am confused with the USING keyword which is used to join two tables in postgres. I first saw it in another SO post Compare two tables in postgres. I checked the manual for postgres 2.6. Joins Between Tables.
I can't understand how postgres identified the user_id to be joined (in the SO post).
Add a comment
|
2 Answers
As the PostgreSQL documentation states:
The USING clause is a shorthand that allows you to take advantage of the specific situation where both sides of the join use the same name for the joining column(s). It takes a comma-separated list of the shared column names and forms a join condition that includes an equality comparison for each one. For example, joining T1 and T2 with USING (a, b) produces the join condition ON T1.a = T2.a AND T1.b = T2.b.
So simply said, in this case both tables have the column user_id and the join is done based on them.
1 Comment
Sami Kuhmonen
@NandakumarV The documentation could use a bit of cross-linking or enhancement, I also had to do some digging to actually find the place where this is explained
A USING clause can be used with an EXECUTE statement in PL/pgSQL language(LANGUAGE plpgsql) for a function, procedure and DO statement.
For example, you can use a USING clause with an EXECUTE statement in a DO statement as shown below:
*Memos:
v1andv2assigned to theUSINGclause can be used multiple times.- You must use
$1and$2instead ofv1andv2in""of theEXECUTEstatement otherwise there is error.
DO $$
DECLARE
v1 TEXT := 'Apple';
v2 TEXT := 'Kiwi';
row RECORD;
BEGIN
EXECUTE 'SELECT $1, $2, $2, $1' INTO row USING v1, v2;
RAISE INFO '%', row; -- INFO: (Apple,Kiwi,Kiwi,Apple)
END
$$;
answered Jan 29, 2024 at 4:20
Super Kai - Kazuya Ito
42.8k23 gold badges258 silver badges257 bronze badges