Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have been programming for a long time, like 13 years (only professionally for about 4). I've become familiar with things like QBasic, Visual Basic, C#, .NET, Java, C, C++, Javascript, JQuery, etc. I can tackle almost any code and figure out what is going on or fix it, as well as create new solutions.

However, no matter how hard I try, or how many times various DB people try and explain to me a solution for SQL, or how to create a SQL query that selects the things I want, I can't seem to 'get it'. What I mean is, I can understand why the new or modified query they write for me works, but I can't seem to get it into my head enough so that I can figure this out on my own or write my own complex queries.

Was there any experience you had, or explanation you heard, that gave you enlightenment about SQL? This is not a yes or no question though, PLEASE share it!

share|improve this question

closed as primarily opinion-based by gnat, Eric King, GlenH7, MichaelT, World Engineer Oct 17 '13 at 0:14

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

I'm guessing you're talking about SELECT statements here? INSERT, UPDATE, DELETE, CREATE and DROP are usually pretty obvious. If you're talking about SELECT, you might want to update the question to not say "SQL" but say "SELECT", since that's sometimes confusing. –  S.Lott Dec 28 '11 at 20:04

9 Answers 9

up vote 8 down vote accepted

I had a really hard time with advanced SELECT statements until I started understanding that there were different query types, and that SQL actually works with result sets, not necessarily tables.

  • A basic query simply selected columns from a table

  • A joined query would combine multiple tables into a result set, then select columns from that result set

  • An aggregate query will build a result set, and then select aggregate values from the result set, such as Sum, Count, Average, etc

  • A grouped query would take a result set, break it down into mini-result sets each containing a subset of the data, and then run some aggregates on each subset of data. The aggregates that are run on each mini result-set of data would be represented by a single row of data in the query results. It took me the longest to understand how grouped queries worked.

  • There's also sub-queries, which is basically one of the above queries which is used as part of another query. So instead of querying a table, you might query the result set of an aggregate query.

It is complicated at first, but keep Googling stuff and don't give up trying to understand it. Someday everything with click together and you'll suddenly see how everything fits together.

share|improve this answer
I had hoped one of these answers would trigger a light-bulb moment in my head, and this one did. Thanks! –  ioSamurai Dec 28 '11 at 21:25
@Ryan Glad I could help! I still remember my "lightbulb moment" when I finally understood how GROUP BY worked :) –  Rachel Dec 29 '11 at 21:06

I have had a good working understanding of SQL simply from working with it over many years.

The real understanding of it came through an Open University course that went through set theory all the way to SQL. With SQL it started with simple queries, going through aggregates then subqueries.

If you understand set theory, how NULL works, how to conceptualize the way the different SQL clauses work (and in what conceptual order) and how to work with aggregates, you will have a very good understanding.

share|improve this answer
+1 for the Set Theory. It's probably not necessary, but it will make understanding SQL much easier. –  FrustratedWithFormsDesigner Dec 28 '11 at 20:06

Rule 1. SELECT is not procedural programming.

Rule 2. The SELECT algorithm must be thought of as pre-built. All you do is customize it.

Rule 3. Don't read it (or write it) in order. The SELECT clauses are ordered to make it read a little bit like an English language specification for the results.
Remember Rule 1. SELECT is not the algorithm. It's a rule for defining the result set from applying a (standard) algorithm.

Think of it like the game of Go Fish.

"Give me all your eights".

You don't tell them how to search their hand for all their eights. You only demand all the cards that match your selection criteria. "WHERE RANK = 8".

share|improve this answer

I'll second Oded's recommendation re set theory, and add:

  • learn normalization at least to 3NF, and then
  • learn to ready query plans

Start with simple selects and joins. Queries are executed using a few (nine, I think) basic operations, none of which are rocket surgery.

share|improve this answer

I gained some understanding of the theory behind SQL at the university. My real working understanding of SQL happened on my first project involving heavy use of SQL, (a medium traffic website,) where I simply had to learn to do things, and I had to learn to do them right, and they had to perform fast, and under heavy load, or else the pages would be loading slowly.

Knowing the theory behind SQL helped avoid many pitfalls that I see self-taught people falling into all the time, but the real eye opener was of course using it in a real world scenario.

share|improve this answer

My best learning experiences were on the job. I've been fortunate to have worked with a number of experienced DBEs* that took the time to explain some of the more complex issues we've had.

In addition, I've hit up Google and found a ton of blogs by SQL developers. I just now found http://sqlblog.com/ that aggregates a number of them. Also, check if there's a SQL user group in your area. You can meet a number of experienced SQL developers there and groups usually have a mailing list where you can ask any level of questions.

  • DBE: Database Engineer, someone who deals with data modeling and crafting queries rather than a DBA, who administers the server software and hardware.

On my own, I work through complex SELECT statements step-by-step. If I know the data I need is in X tables, I start joining them one at a time, pulling back only the relevant data I need from each. Using SELECT TOP 100 to make sure I don't bring back a ton of data. Once I have the core set of data coming back, I start building the WHERE clause a piece at a time.

One tip: much of what you put in your WHERE clause might be better off as part of your JOIN criteria.

share|improve this answer

I learned SQL with MS Access. It comes with a visual query designer that I used for a long time before peaking behind by pressing that scary SQL button which shows the generated SQL. I can still remember the feeling of boldness, starting to write queries in SQL first and switching back to see that visualization of my code :)

You basically see tables as boxes, selected fields as rows in those boxes, JOINed tables as connection between the boxes (attached to the fields that are matched), and additional criteria written under the table that they relate to. I'd recommend starting with such a tool (probably tools like this exist for many DBs, I can only say I really liked the Access one, and it comes with some Office installations) and see that most queries fit into this pattern (of course, at some point it can get more complicated than the interface can show, but when you're facing such situations, you should be fine already). Once you get the hang of that, the rest (ie switching to SQL, if necessary) is just syntax.

share|improve this answer

The order of the keyword clauses in the SQL statement are important. Once you learn this, many things about Select statements will make sense and won't need to be memorized. Examples:

The "ON" clause comes before "WHERE" because that, logic, filtering (however you think about it) will occur first, then the WHERE clause will filter those results. Beware that your WHERE clause could negate the rows you hoped to maintain by using OUTER JOINs.

The "ORDER BY" clause is last. Because the sorting isn't done until the full result is generated, ya column alias can be used to sort. Column aliases fail when used in WHERE, GROUP BY, HAVING, etc. (This is standard SQL and may not hold true for everey RDBMS on the market.).

Work on your SQL outside of your development environment, but in something that is condusive to the database brand you're using (Or some other tool specific for SQL.). You can try some of the graphical tools, but I don't recommmend it. You're going to want to get back to writing SELECT statements in your programming environment and they tend to generate some messy code.

share|improve this answer

I reckon I had a lightbulb moment, but it was so long ago that I'm not entirely sure what it was.

I currently have a working theory that, at least in terms of making simple joins and simple filtering, it can be useful to imagine that you're starting from a cartesian / product join.

(You need to understand what one of those is first, of course: That the select will return every row from (e.g.) the Employee table paired with every row from the Department table. You don't have to limit yourself to two tables, of course.)

This is almost never what you want and generally contains blatantly incorrect data, but that all encompassing list does contain the correct data you want.

It's then your job to filter it down to the rows that you want (or that make sense) by adding everything that's true about the data you want into the WHERE (or ON - but I learned in Oracle without ANSI syntax) clause.

E.g. You only want the rows where the Employee.DepartmentId = Department.DepartmentId, since data with anything else is telling you something about departments that the employee isn't in.

Thinking about it like this also means that when you view the result set and something jumps out at you as not right, you'll generally realise that there's something wrong about the bad rows and that there's something else that's true about the good rows - so you add your extra condition in.

But YMMV! [My theory isn't quite fully developed, of course. I'm thinking that you have to consider that outer joins allow you to add extra rows into the starting (cartesian) result-set if you need them. Then you still have to state everything that's true, like only wanting data where Employee.DepartmentId is NULL ...]

share|improve this answer

protected by GlenH7 Oct 14 '13 at 16:41

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.