Often in SQL examples you will see quote marks around a string that is being operated upon. Generally, a straight string is not often used in general practice. It is more likely to be a variable and more likely yet to be a column. Having or not having quote marks can make a huge difference in the results given by a function. We will use the LEFT() function as an example. The left function is a way to get the leftmost characters from a
string.

SELECT LEFT(‘string’, number_of_characters)

To get the 7 left-most characters from a specific column in every row of a table the following would work:

SELECT LEFT(name, 7) FROM fict_chars

It should be noted for this and for other functions that if the string is in single quotes that SQL will see it as a literal string. If it is not, SQL will see it as a column. Thus, had we used ‘name’, the result would have been “name” for each row, and not the actual name drawn from the specified column.

Just because they look alike...

Generally I am a fan of SQL and how it works, but there is a function that I find irritating. Not because of what it does, but because of its name, “INSERT()”. Why, oh why would you give a function the same name as a statement that already exists? I am sure there is a story behind this and it probably goes back to family squabbles occuring in the 10th century AD, or at least to the 1970s when Chamberlin and Boyce first conceived RDBMS. Suffice it to say that it could cause confusion when quickly glancing over code or when newcomers are learning the language.

Just know that the two are different. The statement is the familiar, much used instruction to the database to add a row to a table.

INSERT INTO table (column1, column2, etc.)
VALUES(‘value1′, ‘value2′, ‘etc.’)

Meanwhile, the INSERT() function is designed to replace text within a string. It does this using the following syntax:

INSERT(‘string’, position, length, ‘inserted_string’)

The first parameter is the string to be operated upon. The position is where the insertion should begin. Length defines how many characters should be replaced. The fourth parameter is the string to be injected.

A simple example:

SELECT INSERT(‘Jane Porter was the heroine in Harry Potter.’, 32, 12, ‘Tarzan’)

Our result, “Jane Porter was the heroine in Tarzan.” Note that the character specified in the second parameter is over-written as are the subsequent 11 characters, 12 characters in all.

The FIELD() function is, in a sense, the inverse of ELT() in that ELT will return a string from a specific numbered position in an array of strings, while FIELD() returns the field position that matches (not the same as “contains”) the string listed as the first parameter. Syntax:

SELECT FIELD(‘string_to_match’, ‘string1′, ‘string2′, ‘etc.’)

Here is the field function in action:

SELECT FIELD(‘Scarlet’, ‘Melanie’, ‘Rhett’, ‘Scarlet’, ‘Ashley’, ‘Scarletta’)

This will return “3″. Though 5 contains “Scarlet”, it is not equivalent to “Scarlet”. Had position 5 also been equivalent to “Scarlet” the result would still have been only “3″. Later matches are ignored.

Both FIELD() and ELT() are a kind of test. They can be used when you are looking for one item out of a group of alternatives. The result can be cranked through a series of if/then statements or switch/case to determine a course of action. This is another place where some of the work done by the server can be taken on by the database.

Read more about SQL Functions.

In the course of writing my book on SQL, I had the opportunity to explore a function I had not heard of before, the EXPORT_SET() function. In my investigations about this function, I discovered that I am not the only person dubious about it. The thing is, every explanation I could find for it was nearly word for word the same down to the examples. This was a good indication that the authors were just parroting each other. When I write about a function I try to use it. At least I will go to the mySQL interface and try out a few variations on it. In this way I get a more intimate knowledge than is possible just reading something someone else has written, especially when that something else is incomplete.

My problem with the EXPORT_SET() function is that I was having trouble figuring out what I could do with it. So I investigated this aspect for an hour or two, surfing here and there I found nothing that would explain why you would ever want to use a function that basically returns an array of bits that comprise a number and lists them in reverse order. Obviously someone wrote this function for a vital purpose. I thought the purpose mignt be found in the name. EXPORT is a clue. After thorough investigation I see it has been used to port data between applications. A few thought experiments made me realize it could also be used in security as a means of encrypting data. I am sure there is more to it.

SQL is broken up into several sub-languages. One of these is DCL which stands for Data Control Language. What it is really about is designating who has permission to do what on a database. Ultimately, this is a security thing. If we could implicitly trust everyone, we would just grant all power to everyone. In the real world of the anonymous internet this would fall into the category of FAIL.

Thus, SQL gives us the ability to GRANT and REVOKE. Actually, on php myadmin in cpannel, you cannot do this with an SQL command, it is done through a UI when the user is created. Basically GRANT gives permission to do various things, and REVOKE takes those permissions away.

The system works logically in that a user has no permissions unless first granted them. Thus we have no REVOKEs without first, somewhere along the line, having a GRANT. The key to GRANTs is to keep them to a minimum. The less access users have to a database the less likely they are to screw things up. Of course, this must be balanced with the needs of the many, which Spock will tell you, out-weighs the needs of the few, we happy few, we band of brothers. Never thought you would see Spock and Henry V quoted in the same sentence did you? Suffice it to say there is a balance.

For most purposes, REVOKEs are used when an empolyee leaves a company or a user loses status (say a subscription lapses).  The syntax for REVOKE follows:

REVOKE privilege
ON object
FROM user or PUBLIC or role

For a GRANT, just replace “REVOKE”.

Okay, let’s get our act together. To find out if a cell is NULL why not just ask…ISNULL? Well, yes, this does work for mySQL. But it isn’t that simple. What if we wanted an easy way to replace the null value with something else? Good idea you say. So the powers that be at mySQL came up with IFNULL(). It looks like this:

SELECT IFNULL(column1, “alternative”) FROM table

So if the expression in column1 turns out to be NULL you can make it something else.

Where it gets a bit confusing is that some applications use ISNULL() the way mySQL uses IFNULL(). And some use a completely different syntax. NVL() is how it looks in Oracle.

But there is another way of doing this available in most applications. It is called COALESCE(). This looks alot like IFNULL (mySQL) but has the added feature that it can take multiple parameters and will in the end return the first one that it finds that is NOT NULL. So you can do something like this:

SELECT COALESCE(column1, column2, etc., “alternative”) FROM table

This will return the first not null param that it runs across. Leave the failsafe for the end.

Normalization is a way to reduce the amount of time it takes for tables to do their processing. It involves reducing tables to the least number of necessary columns. However, this can create a problem when putting the data back together to present desired output. To solve this problem, JOINs were invented in SQL. In a JOIN two columns from two tables are compared to find out if rows should be meshed together, then the rows are combined. It effectively makes one table an extension of another.

For example, we have a list of customers and a list of transactions. We have a customer number for each customer and that customer number is listed in the transaction log. Since we already have all the info we need about our customers in the customer table, we do not need to also store it in the transaction log. All we need do is list the customer number. So when we need to send an invoice to a customer, we can perform a JOIN on the two tables. This will recall the transaction and with the reference to the customer table get the name, address, etc. Alternatively we could also use a JOIN to find all of the transactions by any specific customer.

For the NEWBIE there is a bewildering list of SQL JOINs. Yet they all have a purpose:

First there is the INNER JOIN. This combines the information from two tables based on a comparison of two columns. It outputs only rows that are found in both tables.

A LEFT JOIN will output all the rows in the left table (or first) table and any associated info from the right table. If there is no info from the right, it will leave those cells blank.

The RIGHT JOIN is similar to the left, only in this case, it is all the rows on the RIGHT that will be listed with unfilled cells left blank.

A FULL JOIN is like a RIGHT and LEFT JOIN together. All rows are listed with the appropriate rows meshed. Blanks will be left in rows with unmeshed fields.

To confuse matters perhaps slightly, there is also the UNION. This works alot like a JOIN only it combines two SELECT statements rather than tables. The result is a list of all the unique info from two selections from like table columns.

Hope that clears things up.