4 ms·
SQL Mindset for Programmers
I became much better in Sql over the recent two weeks. I learned a few important things that just blocked me from being able to write Sql before.
The main thing I needed to change mindset from programming in java style my brain just couldn't fit the Sql mindset.
Specifically:
1. Think of new columns as variables.
2. Think of new tables as variables.
3. Think of Sql as functions take some input transform put output in a new column (variable) or new table. And less like a magical big bulk of Sql to pull many data many tables join and output, no, split to functions, to smaller Sql just like functions.
4. When writing Sql try to think first as a sequence of functions, doThis, doThat, doThis, then transform each of them to a small Sql query, each result is in a new column, or a new table.
With the above steps I can start thinking about how to write Sql that I need, before getting the above concept I had a block, I just could not get how to approach Sql.
- HelloNurse 6y agoDo you know you can write actual functions in SQL? Oracle, in particular, has several ways to return table-like objects from functions.
- tomerbd 6y agoI didn't know that, thanks. I will check it out specifically for postgresql I see - https://www.postgresql.org/docs/9.5/sql-createfunction.html https://www.postgresql.org/docs/9.5/sql-createfunction.html
- cafard 6y agoI'm not entirely sure I follow this. However, you might find it convenient to employ "common table expressions" (CTEs) in your queries: WITH cte_a AS (SELECT ... FROM something WHERE ...), cte_b AS (SELECT ... FROM something_else JOIN cte_a ON ... WHERE ...), ... SELECT whatever FROM cte_z JOIN another_table ON ... WHERE ...
- deeteecee 6y agoI mean, i think you just need practice. i feel like you're thinking too literally about the basic process of programming and trying to apply it to sql. Regarding table joins, this visual with venn diagrams on https://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins https://stackoverflow.com/questions/17946221/sql-join-and-di... usually helps me. But again, it's just practice. Try to think of real world cases and then creating relevant join statements for those.