At its core the job of a CMS is to retrieve information from a database and display it to a user. Inside of ExpressionEngine the most common example of this is the Channel Entries module, which at its most basic it looks something like this;
{exp:channel:entries channel="blog"}
{title} {blog_intro} {blog_body}
{/exp:channel:entries}
If you're familiar with EE then you should know instantly what's going to happen here. ExpressionEngine will output the Title, Intro and Content for each entry in the blog channel, one after the next. By adding parameters like "limit=", "orderby=", "categories=", etc we can limit the blog posts that will be shown based on information associated with each entry.
In reality though, the channel entries tag isn't doing much more than providing us a shorthand for requesting data from the database and outputting it to the page.
Here's what EE actually sees with our simple channel entries example;
SELECT t.entry_id, t.channel_id, t.forum_topic_id, t.author_id, t.ip_address, t.title, t.url_title, t.status, t.view_count_one, t.view_count_two, t.view_count_three, t.view_count_four, t.allow_comments, t.comment_expiration_date, t.sticky, t.entry_date, t.year, t.month, t.day, t.edit_date, t.expiration_date, t.recent_comment_date, t.comment_total, t.site_id as entry_site_id, w.channel_title, w.channel_name, w.channel_url, w.comment_url, w.comment_moderate, w.channel_html_formatting, w.channel_allow_img_urls, w.channel_auto_link_urls, w.comment_system_enabled, m.username, m.email, m.url, m.screen_name, m.location, m.occupation, m.interests, m.aol_im, m.yahoo_im, m.msn_im, m.icq, m.signature, m.sig_img_filename, m.sig_img_width, m.sig_img_height, m.avatar_filename, m.avatar_width, m.avatar_height, m.photo_filename, m.photo_width, m.photo_height, m.group_id, m.member_id, m.bday_d, m.bday_m, m.bday_y, m.bio, md.*, wd.*FROM exp_channel_titles AS tLEFT JOIN exp_channels AS w ON t.channel_id = w.channel_idLEFT JOIN exp_channel_data AS wd ON t.entry_id = wd.entry_idLEFT JOIN exp_members AS m ON m.member_id = t.author_idLEFT JOIN exp_member_data AS md ON md.member_id = m.member_id WHERE t.entry_id IN (44,43,3,2,1) ORDER BY t.sticky desc, t.entry_date desc, t.entry_id desc
(there are a few dozen other queries running to fetch information about the user and the site itself, but let's keep things simple for the moment)
While it's wonderful that EE* is taking the burden of writing out these requests for us, there are times where the native EE tags aren't going to cut it for getting exactly the information you need formatted exactly as you need it. The reason for this is simple, EE is complex and it's potential use cases are literally infinite. The channel module (or any other data fetching module) has to have limits to what it can do in order to make the level of abstraction they provide possible. So it's arguably the limitations of ExpressionEngine's various modules that make it possible to use the simplified syntax of a module and it's parameters to build our templates. To be clear, this isn't exclusive to EE, It's how Wordpress, Drupal, Joomla, or any database driven CMS do their magic as well.
Knowing the database logic behind these modules can help you to understand and use them effectively, and yet the limitations get in our way from time to time. Take for example an issue I'd run into on our own EE powered blog. We wanted to have a listing of all authors that have active blog entries, each author was to be listed once, alphabetically and assigned an href based on their member ID. There isn't a clean way to get this data from the channel entries tag, and I came across some pretty out there solutions for presenting this kind of listing (including one that required us to turn on PHP in an embedded template, loop through all entries in the channel, store authors in an array and then pare down that array to only unique username/member_id combinations - overkill performance-wise, and it'd introduce some minor security concerns if done improperly).
If we tried to use the channel entries module to get author names, there's no way to get just one instance of a user, it's going to loop through them based on the entries themselves and present us with a long list of the same few names over and over.
This...
{exp:channel:entries channel="blog"} <a href="/path/to/detail_template/%7Bauthor_id%7D">{author}</a>{/exp:channel:entries} Would've produced...
<a href="/path/to/detail_template/1"></a><a href="/path/to/detail_template/1"></a><a href="/path/to/detail_template/2">Kevin</a><a href="/path/to/detail_template/3">Mark</a><a href="/path/to/detail_template/2">Kevin</a><a href="/path/to/detail_template/3">Mark</a><a href="/path/to/detail_template/3">Mark</a><a href="/path/to/detail_template/4">Kiel</a><a href="/path/to/detail_template/1">Evan</a> ...
So yeah, exp:channel:entries isn't going to cut it. Time to get a bit closer to the metal with the help of exp:query.
This is a great example of a problem that's best left to the exp:query module. We need a bit of data that isn't readily available, and we don't want to jump through a bunch of hoops or waste a lot of processor cycles to get it.
With exp:query we can pass our own SELECT statements directly* to the EE database and output returned rows of data in the same tag-pair / loop format that we're used to seeing with exp:channel:entries.
*almost directly, EE still wants to save us from our own idiocy if it can, which is why only the relatively safe SELECT statement is the only one we're allowed to use here (DELETE, UPDATE or INSERT won't work here)
This isn't going to be an explanation of how to build SQL queries, but in case you're curious, the SQL we need to fetch our authors is basically something saying "Get a list of the unique usernames the associated member id's for each author who has open posts in the blog channel (in this case, that's channel_id 1)."
Here's the SQL for our unique author listing example...
SELECT DISTINCT exp_members.username, exp_members.member_id FROM exp_members INNER JOIN exp_channel_titles ON exp_members.member_id = exp_channel_titles.author_id WHERE exp_channel_titles.`status` = 'open' AND exp_channel_titles.`channel_id` = 1 ORDER BY screen_name ASC
Now, because {username} and {member_id} can be interpreted by ExpressionEngine natively, we're going to ask our SQL to give them new reference names when it returns the data just to avoid stepping on toes here. SELECT DISTINCT exp_members.username AS author_username, exp_members.member_id AS author_member_id FROM exp_members INNER JOIN exp_channel_titles ON exp_members.member_id = exp_channel_titles.author_id WHERE exp_channel_titles.`status` = 'open' AND exp_channel_titles.`channel_id` = 1 ORDER BY screen_name ASC To use the output of this query in an EE template, we just have to use the exp:query tag, and provide our SQL as a parameter like so;
{exp:query sql=—SELECT DISTINCT exp_members.username AS author_username, exp_members.member_id AS author_member_id FROM exp_members INNER JOIN exp_channel_titles ON exp_members.member_id = exp_channel_titles.author_id WHERE exp_channel_titles.`status` = 'open' AND exp_channel_titles.`channel_id` = 1 ORDER BY screen_name ASC"}<a href="%E2%80%94/path/to/detail_template/%7Bauthor_member_id%7D/%E2%80%94">{author_username}</a>{/exp:query}
When this tag set is processed by EE the output will be;
<a href="/path/to/detail_template/1">Evan</a><a href="/path/to/detail_template/5">Jason</a><a href="/path/to/detail_template/2">Kevin</a><a href="/path/to/detail_template/4">Kiel</a><a href="/path/to/detail_template/3">Mark</a>
Which is exactly the data we wanted, in exactly the format we wanted, without any additional templates, embeds or plugins. This is a simple example of using the query module, but it's possible to use much (much) more complicated select statements to draw information and display it in ways that would make your head spin. Exp:query doesn't get a lot of fanfare, but it's easily one of the most useful and powerful modules available in your toolkit.
By the way…
If you're developing an interest in becoming a MySQL guru, I'd highly recommend getting ahold of PHP & MySQL: The Missing Manual by Brett McLaughlin. It's earned a place on my shelf, and it's on the short list of books I instantly recommend to new developers.