7. The IF function - no explicit table names required?

SQL
1

I’m understanding the use of the IF function in this, however it’s the lack of table names or aliases throughout that’s confusing me.

Given that there is a ‘product_id’ in both tables here, why is no error message being thrown out?

nonamecollision
nonamecollision932×319 34.1 KB

2

I notice that if I do this…

SELECT *
FROM products
JOIN order_items USING (product_id)

then there is only one product_id column in the result set. So I think that when we say SELECT product_id, we don’t need to disambiguate, because there is only that one product_id field. However if both tables share another column name, and that name isn’t used in the USING to join the tables, we must disambiguate it in the SELECT statement. For example if both tables have a createdAt column, we can’t say SELECT createdAt, we would need to SELECT products.createdAt or SELECT order_items.createdAt, otherwise we would see the error that Column 'createdAt' in field list is ambiguous.

1 Like
3

Thank you, eelsholz - the use of the USING clause is indeed the difference.

This works:

SELECT product_id
FROM products
JOIN order_items USING (product_id)

This produces an ‘ambiguous column’ error:

SELECT product_id
FROM products
JOIN order_items     
    ON products.product_id = order_items.product_id
1 Like