SQL asterisk(*) all possible uses

Question

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.

Answer 1

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!

Answer 2

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