SQL Query Guide


By Yotam
Follow

This is a basic guide to SQL queries; this will help understand and compose SQL queries to retrieve information from the database.

The Basic SQL Query

The SQL query looks like this:

SELECT *
FROM {TABLE_NAME}
WHERE 1

Let's break that query down:

  • SELECT represents the columns we want to be retrieved; in this case, the asterisk represents means "all". When we use SELECT *, we tell the query to show us all columns.
  • FROM represents the table from which want the information retrieved; FROM Site, for example, tells the database to show us the columns in the "Site" table.
  • WHERE is the conditioning section - this is where we specify how to filter out specific values to be displayed.

That means that the query above, SELECT * FROM {TABLE_NAME}WHERE 1, is translated to "show me all columns and values in the table called {TABLE_NAME}". 

Remember: while selectors (SELECT, FROM, WHERE and so on) are not case sensitive, table and column names ARE.

FYI, the order of the query's resolution is as follows: FROM -> WHERE -> SELECT. Defining, conditioning, refining.

 

Conditioning a Query

Let's take a basic query to show us information about sites on Incapsula's service:

SELECT *
FROM Site
WHERE 1

The result will be ALL information about ALL sites that exist and ever existed on Incapsula's system; just for scale, the result of this query at the time of this writing is 1,179,234 rows, meaning 1,179,234 websites.

basic.png

Let's condition it by removing sites that were deleted:

SELECT *
FROM Site
WHERE Site.isDeleted = 0

We added a condition: Site.isDeleted = 0; that tells the database to look for the column "isDeleted" in the table "Site", and show only results where the value of that column equals 0 (meaning "false").
By changing WHERE 1 ("display all") to WHERE Site.isDeleted = 0 we tell the database to show us only non-deleted sites. For comparison, this query returns a mere 305,142 rows.

conditioning.png

Refining a Query

We can further refine our results by choosing what columns we want to have displayed. Let's say we want only the sites' internal IDs - we change the SELECT value from * to Site.id, so now our query looks like this:

SELECT Site.id 
FROM Site
WHERE Site.isDeleted = 0

You'll notice the number of results remain the same - 305,142 rows; that's because we didn't change the condition for the results, only refined the criteria for the information we want displayed. What we get is 305,142 rows but only a single column.

refining.png

We can have multiple columns displayed in a singe refined query; in order to achieve this, what we do is add all the column names we want and separate them with a comma (,). Say we want the sites' IDs and their URLs; our query will now look like this:

SELECT Site.id, Site.displayName 
FROM Site
WHERE Site.isDeleted = 0

We can also assign custom names to columns using the "AS" selector and putting the designated names in single or double quotes, like so:

SELECT Site.id AS "Site ID", Site.displayName AS 'Yotam is the absolute best, you guys' 
FROM Site
WHERE Site.isDeleted = 0

refining2.png

The columns are ordered left-to-right according to the order in which they appear in the SELECT selector; if SELECT * is used, columns will be ordered according to the table's default order.

 

Joining Tables

Sometimes we need information from more then one table. We *could* run multiple queries and compare results, but there is a better, more convenient and much more effective way - table joining. Let's take our previous query:

SELECT Site.id AS "Site ID", Site.displayName AS 'Yotam is the absolute best, you guys' 
FROM Site
WHERE Site.isDeleted = 0

We can add a JOIN selector to have information pulled from more then one table. We want to add the the JOIN selector between the first and second step: after FROM but before WHERE, since we want the WHERE condition run on all tables.

Say we want to gather the same information as before, but instead of displaying information on all sites currently on Incapsula's service, we want sites from a specific account. Let's also add additional SELECT selectors to display Account information, so our query will then look like this:

SELECT Account.id AS 'Account ID', Account.name AS 'Account Name', Site.id AS "Site ID", Site.displayName AS 'Yotam is the absolute best, you guys' 
FROM Site
JOIN Account ON Account.customerAdmin_id = Site.customerAdmin_id
WHERE Site.isDeleted = 0
AND Account.id = '14048'

joining.png

Let's look at the added JOIN selector: 

JOIN Account ON Account.customerAdmin_id = Site.customerAdmin_id

JOIN allows the query to run the condition on more then one table, so we tell the query which table we want to add - in this case, the Account table. We cannot, however, join any tables we want - the tables must have a joining column; in this case, the customerAdmin_id acts as the joining column between the Site and Account tables. That type of column is called a Key.

You can use multiple JOIN selectors one after another.

We also added the AND selector, as it allows us to have multiple conditions; AND selectors are part of the conditioning step, after the WHERE selector; there's no real limit to how many AND selectors you can add.

 

We have a KB section with sample queries; you can find it here.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments

Powered by Zendesk