CODEWITHSUNDEEP
sql
was_777
was_777

Reputation: 711

SQL asterisk(*) all possible uses

I have a simple question. I want to list down all the scenarios where we can use the keyword asterisk(or star) *.

I am known of only these scenarios:

Select * from Customers;
Select Count(*) from Customers;
Select Customers.quantity * Customers.price from Customers;

I've searched all over the internet but didn't find any other use case.

Also, the scenarios where we can use * with a column in select query.

Edit: Ok as @Lucas Eder requested to know my use-case, here it is. I've got a program which accepts SQL query and store it in DB. Before storing, it does a validation not allow to create select * and Count(*) queries. Other than that it should allow all other queries. So that's the reason I want to know other scenarios where * is used so that I can whitelist them.

Upvotes: 11

Views: 12096

Answers (3)

pwilcox
pwilcox

Reputation: 5753

It is used in sql-server for xml. In the code below, aliasing the subquery as '[*]' means that COLUMN_NAME and DATA_TYPE xml nodes output on the same level as TABLE_SCHEMA and TABLE_NAME. Compare with naming the column '[cols]':

SELECT 
    t.TABLE_SCHEMA,
    t.TABLE_NAME, 
    (
        SELECT 
            c.COLUMN_NAME,
            c.DATA_TYPE
        FROM INFORMATION_SCHEMA.COLUMNS c
        WHERE c.TABLE_SCHEMA = t.TABLE_SCHEMA
        AND c.TABLE_NAME = t.TABLE_NAME
        FOR XML PATH(''), type
    ) [*] -- compare with [cols]
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_NAME LIKE 'cmn%'
FOR XML PATH ('Record'), ROOT('Data')

Outputs:

<Data>
  <Record>
    <TABLE_SCHEMA>dbo</TABLE_SCHEMA>
    <TABLE_NAME>Table1</TABLE_NAME>
    <COLUMN_NAME>Col1</COLUMN_NAME>
    <DATA_TYPE>int</DATA_TYPE>
    <COLUMN_NAME>Col2</COLUMN_NAME>
    <DATA_TYPE>int</DATA_TYPE>
  </Record>
  <Record>
    ...
  </Record>
</Data>

As opposed to (using [cols]):

<Data>
  <Record>
    <TABLE_SCHEMA>dbo</TABLE_SCHEMA>
    <TABLE_NAME>Table1</TABLE_NAME>
    <cols>
      <COLUMN_NAME>Col1</COLUMN_NAME>
      <DATA_TYPE>int</DATA_TYPE>
      <COLUMN_NAME>Col2</COLUMN_NAME>
      <DATA_TYPE>int</DATA_TYPE>
    </cols>
  </Record>
  <Record>
    ...
  </Record>
</Data>

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 220932

Fun question!

Here's what jOOQ knows about the various SQL dialects (looking at its sources):

Repeating yours (with some comments):

-- Unqualified asterisk
SELECT * FROM t;

-- Unqualified asterisk with useful Google BigQuery extension
SELECT * EXCEPT (a, b) FROM t

-- Asterisk in COUNT
SELECT COUNT(*) FROM t;

-- Multiplication operator for numbers
SELECT a * b FROM t;

-- Multiplication operator for intervals / numbers
SELECT INTERVAL 1 DAY * 3 FROM t;

Other cases jOOQ knows:

-- Qualified asterisk
SELECT t.* FROM t;

-- Multiline comment syntax
SELECT /* important column */ a FROM t;

-- Oracle hint syntax
SELECT /*+FIRST_ROWS*/ a FROM t;

-- Old SQL Server outer join syntax (no longer supported)
SELECT * FROM t, u WHERE t *= u;

-- Oracle precision / scale wildcard
CREATE TABLE t (a NUMBER(*), b NUMBER(*, 2));

-- PostgreSQL syntax for case insensitive pattern matching (ILIKE) 
-- (there are many more operators)
SELECT 'abc' ~~* 'A%'

Other cases I know:

-- MATCH_RECOGNIZE pattern matching
SELECT * FROM t MATCH_RECOGNIZE ( ... PATTERN X Y* Z ... )

-- Oracle 21c's projecting everything into JSON
SELECT JSON_OBJECT(*) FROM t

String literal contents, which are specified and parsed as well:

-- Regular expressions (the asterisk is in a string literal, but it's still worth noting)
SELECT regexp_replace(a, 'a*', '') FROM t;

-- Similar predicate (again, it's in a string literal but the format is specified)
SELECT 'abc' SIMILAR TO 'a*'

-- JSON Path contents (there are quite a few possible asterisks here)
SELECT JSON_QUERY(col, '$.*') FROM t;

-- XPath contents
SELECT XMLQUERY('/x/*' PASSING t.xmldoc) FROM t

Esoteric cases:

The ISO/IEC 9075-2:2016(E) SQL standard specifies in 21.6 <embedded SQL Fortran program> [sic!]

<Fortran type specification> ::= CHARACTER [ <asterisk> <character length> ] [ CHARACTER SET [ IS ] <character set specification> ]

Yes. You asked for it!

Upvotes: 18

Lukasz Szozda
Lukasz Szozda

Reputation: 175756

Oracle: * as string literal's delimeter:

SELECT q'*O'Brien*' AS name FROM dual;

Output:

+---------+
|  NAME   |
+---------+
| O'Brien |
+---------+

db<>fiddle demo

Hive: REGEX Column Specification:

SELECT `commonPrefix*` FROM table_name

* as field terminator for all kind of SQL dialects that support external tables and/or COPY operation.

Azure Synapse: * as placeholder

SELECT p.filepath(1) AS [year],
       p.filepath(2) AS [month],
       COUNT_BIG(*)  AS cnt
FROM OPENROWSET(
   BULK 'https://<sth>.windows.net/some_name/pyear=*/pmonth=*/*.parquet'
   ,FORMAT = 'PARQUET'
) AS p
WHERE p.filepath(1) IN ('2018','2019','2020');

SQL Server compound operator: @var *= x syntax as syntactic sugar for @var = @var * x

DECLARE @var INT = 1;
SELECT @var *= 2;
SELECT @var;
-- 2

db<>fiddle demo

EDIT:

"I've got a program which accepts SQL query and store it in DB. Before storing, it does a validation not allow to create select * and Count(*) queries. Other than that it should allow all other queries. So that's the reason I want to know other scenarios where * is used so that I can whitelist them."

Banning * does not prevent user from grabbing all columns. Example

SELECT * FROM t;
<=>
TABLE t;
-- PostgreSQL, MySQL

db<>fiddle demo

Upvotes: 3

Related Questions