System requirements should specify what the user needs, not how to implement it. Unfortunately, requirements are often written by very expert users whose ideas about how to do things seep into the requirements. I was recently involved with a project that specified a very advanced query builder - complete with AND, OR, MINUS, and parentheses. It was clear that the user specifying this functionality was SQL literate and he wanted to share the power of SQL with everybody else. Experience shows that these very advanced query functions are
If you face these requirements, push back. Offer SQL SELECT access to the very few super users who can write SQL, and provide a much simpler and usability tested query interface for everyone else.
I wonder if you should be a little more specific here. There is nothing inherently wrong with an outer join per se. In fact sometimes due to poor database design it's the only way to construct a query that returns the correct rows. Also SQL is a set oriented language - so you really cannot take away set operators like MINUS. It's been my experience that business users are far better at SQL than hard core programmers, because the latter learned and have almost exclusively used record oriented logic for so long that they simply don't get set oriented logic - and hence they write far less efficient SQL than some business users. In fact since the business users know the data they often do a pretty darned good job. So let's be careful who we throw under the bus :)
A “query builder” that dynamically generates SQL statements is a tacit invitation to run untested SQL queries against the production database. In general, that would be a bad idea but sometimes that’s what the users really need; for example, users may need to create customer “segments” for email campaigns. See blog.mailchimp.com/introducing-hairball-an-air-app-for-really-complicated-mailchimp-lists in which the solution that is suggested is for the users to download their email lists to their own computer and create the customer segments using their own computing resources to avoid “turning the production databases into a hairball of a mess.”
I don't have a problem with outer joins or any other part of the SQL language, and agree that power users can write good SQL. My issue is with requirements that ask for a point-and-click query builder.
The people who really know SQL should be given readonly SQL access. Preferably not to production, but as Iggy states, that's sometimes what they need. But the people who don't know SQL should have a really simple interface, not a full query builder.