Robert Gravelle

There has been much debate as to whether derived tables, views, or temporary tables are actually faster in terms of database performance. The fact is that there is no simple answer, as different queries require different optimizations. Rob Gravelle presents and evaluates some uses for derived tables that highlight their value in many applications. In […]

The go to solution for removing duplicate rows from your result sets is to include the distinct keyword in your select statement. It tells the query engine to remove duplicates to produce a result set in which every row is unique. Did you know that the group by clause can also be used to remove […]

Rob Gravelle explores the pros and cons of normalization with a particular focus on the ramifications of third normal form normalization on data extraction by SELECT query. Extracting data from a normalized database structure requires special care. This holds especially true where tables share a many-to-many relationship. In this article, we’ll be looking at the […]

In data modeling, explaining how each table links to another is known as cardinality. Knowing how to establish table cardinality is a key skill in database design because it can identify areas where the normalization process may have gone awry. This article looks at each relationship type more closely. In data modeling, explaining how each […]

When joining tables on common fields, if you’re not diligent in the use of AND-OR combinations in the criteria clause, be prepared for unexpected results! Learn how to break filtering criteria into individual steps and how to group AND-OR statements to most effectively isolate the data that you wish to retrieve. The following code can […]

To minimize headaches that can accompany long field lists, DBAs employ a range of techniques. The specific techniques covered today are used to disambiguate between duplicate column names. Don’t be too quick to judge this as basic stuff. You just might find that there are a few things you don’t know on this subject. In […]

Cartesian Products usually don’t provide useful information and often result in mistakes that can hurt your database developer career. Learn to spot Cartesian Joins and banish them from your SELECT queries forever. I’ll never forget one of my first SQL assignments as a young junior developer in the Federal Government. I had to produce a […]

The SELECT statement is without question the most complex amongst MySQL’s data manipulation statements. This article covers the syntax of the SELECT statement, exploring its many optional clauses. The SELECT statement is without question the most complex amongst MySQL’s data manipulation statements. Not surprising when you consider that the foremost purpose of structured query language […]

Rob Gravelle explores the MySQL UPDATE statement, which is used to modify existing records in a table. Among its many features, he looks at how to update multiple tables, avoiding mistakes, and how to limit how many rows are updated. In the MySQL INSERT Statement Variations article, we explored the use of MySQL INSERT…SELECT syntax […]

The Data Manipulation Language (DML) SELECT…INTO command is unsupported in MySQL. However, MySQL does provide the INSERT…SELECT statement. Rob Gravelle discusses this, and other variations of the INSERT statement. In the MySQL Data Manipulation and Query Statements article, we looked at two variations of the INSERT INTO statement. If you recall, we utilized the INSERT […]

The Data Manipulation Language (DML) includes the major commands, INSERT, UPDATE, DELETE, and SELECT…INTO. Today’s article focuses specifically on the uber-useful INSERT, UPDATE, DELETE, and SELECT statements. Standard SQL is an American National Standards Institute (ANSI) standard, but many database vendors have created their own proprietary extensions and different versions of SQL. The types of […]

Rob Gravelle demonstrates a solution for passing MySQL dynamic field names to the group_concat function using a combination of stored proc input parameters, string concatenation, and prepared statements. Although there is no direct support for dynamic declaration of column and table names in MySQL stored procedures, by utilizing user variables and prepared statements we can […]