What does % in this WHERE clause do?
It is being used as a Modulo Operator; returning the remainder of a number divided by another.
In your example, the WHERE clause is limiting the results to only those where the Col1 value divided by 3 leaves a remainder of 1. (e.g. 4,7,10, etc.)
It's the Modulo operator.
Returns the remainder of one number divided by another.
% in SQL can be used in two different formats. If it's used in a string with LIKE
then it's a wild card, for example:
WHERE col_name like '%test%'
That would mean find everything in col_name
which has the word "test" in it.
But in this specific operation the % symbol is being used as a modulo operator. Modulo (on a calculator normally shown as MOD button) returns the remainder. It can be quite a useful tool if using a for loop to display data and you want to count columns - I have recently used the modulo operator when drawing up a web page. Mine was in PHP, but this is an example:
$count = 0 ;
$records = array () ;
for ( $a = 0 ; $a < 100 ; $a++ )
$records[$a] = $a ;
foreach ( $records as $record )
{
if ( $count % 3 == 0 )
echo '<p>Three items</p>' ;
if ( $count % 10 == 0 )
echo '<p>Ten items</p>' ;
echo '<p>'.$record.'</p>' ;
$count++ ;
}
This would basically output 1 - 100 and every three items it would output "Three items", and every ten items it would output "Ten Items" because any other numbers would return a value e.g.:
5 / 10 = 0.5 (5 % 10 = 5)
2 / 3 = 0.666... (2 % 3 = 2)
50 / 10 = 5.0 (50 % 10 = 0)
9 / 3 = 3.0 (9 % 3 = 0)