Join on field names
In a previous post I described how to write SQL that would be readable and maintainable.
It turns out that PostgreSQL has feature that can make it even better:
Let's take an modified example of an earlier post.
CREATE TABLE security ( badge_number SERIAL NOT NULL PRIMARY KEY, --... ); CREATE TABLE employee ( employee_id SERIAL NOT NULL PRIMARY KEY, manager_id INTEGER, badge_number CHAR( 5 ) REFERENCES security( badge_number ), --... ); CREATE TABLE hardware ( employee_id SERIAL PRIMARY KEY, FOREIGN KEY (employee_id) REFERENCES employee( employee_id ) --... );
A simple query could be:
SELECT employee.*, hardware.* FROM employee employee LEFT OUTER JOIN hardware hardware ON hardware.employee_id = employee.employee_id
Using to simplyfy it further:🔗
SELECT employee.*, hardware.* FROM employee employee LEFT OUTER JOIN hardware hardware USING( employee_id )
That works because both tables have the same field name.
If you are the process of designing a new database, please concider to qualify the Joinable fields.
CREATE TABLE something( id SERIAL PRIMARY KEY, name VARCHAR( 64 ) NOT NULL, ... ); CREATE TABLE otherthing( id SERIAL PRIMARY KEY, FOREIGN KEY( something_id ) REFERENCES something( something_id ) ... );
CREATE TABLE something( something_id SERIAL PRIMARY KEY, name VARCHAR( 64 ) NOT NULL, --... ); CREATE TABLE otherthing( otherthing_id SERIAL PRIMARY KEY, FOREIGN KEY( something_id ) REFERENCES something( something_id ) --... );
- You can use 'USING'
- If you are using a query tool (e.g. msqry32.exe), it automatically joins correctly 3 You still have the freedom to use the traditional syntax. You still would need to do that if you'd Join 'employee.employee_id = employee.manager_id'.
- If you are using a ORM (SQLAlchemy, Doctrine, Hibernate, ..), you need to fiddle with the table meta settings.