4 ms·
That's pretty much it. If you want to do it with a minimum of new syntax, you could defer naming the join until the select, as you normally would: CREATE TA
by mtdewcmu 13y ago
That's pretty much it.
If you want to do it with a minimum of new syntax, you could defer naming the join until the select, as you normally would:
CREATE TABLE emp (
emp_id INTEGER PRIMARY KEY,
manager_id INTEGER FOREIGN KEY REFERENCES emp,
hr_rep_id INTEGER FOREIGN KEY REFERENCES emp,
name VARCHAR(50),
...
);
SELECT emp.name AS "Employee", manager.name AS "Manager"
FROM emp
NAMED JOIN emp.manager_id AS manager;
I hadn't given any thought to the syntax, but that looks pretty nice and tidy.
Edit:
NAMED JOIN isn't really appropriate anymore with that change in syntax. Something like DECLARED JOIN might be better.
- dragonwriter 13y agoYeah, I originally had thought to do it more like that, but I figured that the big win from having language-level support for something like this was the ability to have clear semantic definitions in the declared relations and concise queries, so I thought naming in the Foreign Key was the best way. But I can see it either way.
- mtdewcmu 13y agoI'd leave it to experts to design the best syntax, because they could foresee all the possible ramifications of different choices. I think we've clearly articulated the ends and some potential means.
- mtdewcmu 13y agoYou might have to give the join a name in the query in either case, because what if you want to go two levels deep, e.g. join in your boss's boss? Both can't be manager. All I'm interested in is making the queries more concise. Otherwise, I like the loose coupling between related entities in the relational model. I would have been hesitant to add join information to the table declaration if it wasn't already there. But since it's already there as a constraint, you might as well get all the mileage you can out of it.