💻 Guide: SQL & Relational Databases
WRITTEN BY: RYAN ZERNACH
SUMMARY — If you, like me, firstly learned to write Python before learning to write SQL, then learning to write SQL seems a bit redundant — another way to do the same data wrangling/refining functions as Python's core dataframe wrangling library, Pandas. However, at its core, SQL is more than that: it's provides accessibility to relational databases.
✅ USE — from which database should the data be queried (if simultaneously connected to multiple databases)
✅ SELECT — from which columns in the table should the data be queried
✅ FROM — from which tables within the database should the data be queried
✅ WHERE — to filter data being queried using logical operators
▻ AND — both conditions are met; higher order of precedence than OR
▻ OR — either conditions are met; lower order of precedence than AND
▻ NOT — negates the condition by querying for the inverse
▻ IN — the list form of "or"; to filter results based on if the query is in the list
▻ BETWEEN/AND — to select a range of values where >= x and <= y
✅ ORDER BY —
Good reliable databases are a big and hard problem, and while I'm sure there are developments in dataframe frontends for them, the Achilles heel of dataframes is that they generally want all your data in memory at the same time. Relational databases only need the keys/relations in memory, and can scan for the rest on disk, which lets them aggressively optimize queries and have superior performance.