To be honest, it’s remarkably simple for what it’s doing. There’s a ton of details that are abstracted away. Databases are massively complex things, yet we can write simple queries to interact with them, with semantics that are well-understood and documented. I think, like anything else, it requires a bit of effort to learn (not a lot, though). Once you do, it’s pretty easy to use. I’ve seen many non-technical people learn enough to write one-off queries for their own purposes, which I think is a testament to its simplicity.
Oneliners are simple I give that to you, but then you have those incomplete tables and whatnot. Like take all entries from A and join B on A.id and B.id, set the result to some default if B doesn’t exist.
You are surely going to whip up a perfect string of SQL but I’d struggle.
SELECT a.*, COALESCE(b.some_col, 'some_default_val') as b_result
FROM a LEFTJOIN b ON (a.id = b.id);
This will produce at least 1 row for every row in a, and if a.id doesn’t match any b.id, the value of b_result will be 'some_default_val'.
Not sure if that’s exactly what you were describing (since it was a little ambiguous), but that’s how I interpreted it.
Ultimately it’s just a matter of investing a little time to learn it. It’s not fundamentally difficult or complex, even though you certainly can write very complex queries.
To be honest, it’s remarkably simple for what it’s doing. There’s a ton of details that are abstracted away. Databases are massively complex things, yet we can write simple queries to interact with them, with semantics that are well-understood and documented. I think, like anything else, it requires a bit of effort to learn (not a lot, though). Once you do, it’s pretty easy to use. I’ve seen many non-technical people learn enough to write one-off queries for their own purposes, which I think is a testament to its simplicity.
Oneliners are simple I give that to you, but then you have those incomplete tables and whatnot. Like take all entries from A and join B on A.id and B.id, set the result to some default if B doesn’t exist.
You are surely going to whip up a perfect string of SQL but I’d struggle.
Heh yeah that’s pretty straightforward:
SELECT a.*, COALESCE(b.some_col, 'some_default_val') as b_result FROM a LEFT JOIN b ON (a.id = b.id);This will produce at least 1 row for every row in
a, and ifa.iddoesn’t match anyb.id, the value ofb_resultwill be'some_default_val'.Not sure if that’s exactly what you were describing (since it was a little ambiguous), but that’s how I interpreted it.
Ultimately it’s just a matter of investing a little time to learn it. It’s not fundamentally difficult or complex, even though you certainly can write very complex queries.