Ambiguity in the SQL query

There are two tables: table customer consists of information about customers and table payment consists of information about payments. Primary key customer_id in the customer table is a foreign key in the table payment_id. The following two queries return identical results:

customer

payment

customer_id

customer

payment_id

SELECT
payment.customer_id,
last name,
amount
FROM customer
INNER JOIN payment ON customer.customer_id = payment.customer_id

SELECT
customer.customer_id,
last_name,
amount
FROM customer
INNER JOIN payment ON customer.customer_id = payment.customer_id


The only difference between the queries is in the first argument in the SELECT clause: payment.customer_id vs customer.customer_id. As the customer_id is the column on which the tables are joining on, the distinction between payment.customer_id and customer.customer_id seems meaningless. However, if I try to omit the table in the query:

SELECT

payment.customer_id

customer.customer_id

customer_id

payment.customer_id

customer.customer_id

SELECT
customer_id,
last_name,
amount
FROM customer
INNER JOIN payment ON customer.customer_id = payment.customer_id


I receive

[42702] ERROR: column reference "customer_id" is ambiguous

Could you please describe where is ambiguity in the query?

6 Answers
6

Just because two columns match using the equality test, does not mean that they have the same value.

The two columns could be different types eg integer and float, or numeric etc.

Or they could be citext which does case insensitive comparisons (one table could have 'RedRum' and other 'redruM').

citext

'RedRum'

'redruM'

often the join condition might not be a strict equality (eg a network range comparison, or prefix match)

In all these cases which table you use for the result column is significant.

if you're doing an outer join table name is again significant.

Postgresql does not know when = means that the table can be implied and when it cannot, it requires it always.

=

Rule of thumb, when joining tables specify the table of every column you use in the query. that way things won't break if someone adds some columns to the other table.

You answered your own question by omitting the table in the select statement. By not specifying it, SQL doesn't know Which table is customer_id referring to.

customer_id

Could you please describe where is ambiguity in the query?

Logically there is no ambiguity in the query, as both the columns must have the same values. However, an ambiguity may appear when you use LEFT JOIN instead of INNER JOIN, e.g.:

LEFT JOIN

INNER JOIN

INSERT INTO customer (customer_id, last_name) VALUES
(1, 'Smith'),
(2, 'Jones');

INSERT INTO payment (customer_id, amount) VALUES
(1, 100);

SELECT
customer.customer_id,
payment.customer_id,
last_name,
amount
FROM customer
LEFT JOIN payment ON customer.customer_id = payment.customer_id

customer_id | customer_id | last_name | amount
-------------+-------------+-----------+--------
1 | 1 | Smith | 100
2 | | Jones |
(2 rows)


The parser just follows general rules and does not analyse a query to find out when a potential ambiguity can come to light.

It is a good practice to always prefix your column with table/subquery alias.

But in your case(only PK/FK names are shared among both tables) you could also use USING clause:

USING

SELECT
customer_id,
last_name,
amount
FROM customer
JOIN payment USING(customer_id);


DBFiddle Demo

There is also third possible solution, but I strongly recommend not to use it:

SELECT
customer_id,
last_name,
amount
FROM customer
NATURAL JOIN payment


NATURAL JOIN

NATURAL JOIN

NATURAL JOIN

NATURAL JOIN

Legacy joins such as INNER JOIN create duplicate columns. Using INNER JOIN in your query generates two columns named customer_id. The SQL language has a workaround for this: you must prefix the column with a range variable, as others have suggested here (though using the misleading term 'table alias').

INNER JOIN

INNER JOIN

customer_id

Thankfully, the SQL language also has a fix for this problem: NATURAL JOIN creates no duplicate columns, therefore you don't need to disambiguate them:

NATURAL JOIN

SELECT
customer_id,
last_name,
amount
FROM customer
NATURAL JOIN payment


Joins that produce duplicate columns remain because nothing is ever removed from the SQL language (the "shackles of compatibility"). But you don't need any join other than NATURAL JOIN.

NATURAL JOIN

The idea is that your data element names mean the same thing throughout your data dictionary e.g. amount means one thing (pertaining to payments) and one things only (there is no amount that pertains to customers or any other type).

amount

amount

Sometimes you may need to 'project away' columns you don't want to participate in NATURAL JOIN e.g.

NATURAL JOIN

WITH
C AS ( SELECT customer_id, last_name FROM customer ),
P AS ( SELECT customer_id, amount FROM payment )
SELECT
customer_id,
last_name,
amount
FROM C
NATURAL JOIN P


This also 'defends' your code e.g. in the unlikely event of someone adding a last_name attribute to payments.

last_name

The error means there are two columns have same name customer_id, let DB engine didn't know Which one column do you want to query.

customer_id

You need to explicitly tell the DB engine the name of the column you want to query.

Tables may be added a new column after table has been created, the new column may be the same as the old column name if you didn't clearly specify SELECT table columns of the query in the selection the will be an error on your original query.

SELECT

Here is some suggestion for you

You can give query table an Alias name, let your query Clearer.

Clearly specify SELECT table columns of the query in the selection from tables name, because of tables

SELECT

If last_name column in payment table and amount column in customer

last_name

payment

amount

customer

you can do this.

SELECT
c.customer_id,
p.last_name,
c.amount
FROM customer c
INNER JOIN payment p ON c.customer_id = p.customer_id


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.