SELECT

DOCS • LANG

---

The SELECT statement.

See APIS ➞ client.query(), table.select()

Section	Description
Basic Select	Run a basic SELECT query.
The WHERE Clause	Specify conditions against which to filter records.
The ORDER BY Clause	Sort output rows by specific criteria.
The LIMIT Clause	Restrict the number of rows returned by a query.
The OFFSET Clause	Skip a specified number of rows before returning rows.
The JOIN Clause	Select records from one table and tie in relevamt records from related tables.
The GROUP BY Clause	Group rows together by specific criteria for use with aggregate functions.
The HAVING Clause	Filter groups based on the result of aggregate functions.
The WINDOW Clause	Define reusable window specifications for window functions.
Structuring Syntax	Structure your output data without all the rough work.
Magic Paths	Express relationships graphically.

Basic Select

Select all fields from all records:

// (a): SQL syntax
const result = await client.query(
    `SELECT *
    FROM public.users`
);

// (b): Function-based syntax
const result = await client.database('public').table('users').select();

The WHERE Clause

Use the WHERE clause to specify conditions against which to filter records.

Example 1:

Find records that satisfy two specific conditions:

// (a): SQL syntax
const result = await client.query(
    `SELECT *
    FROM public.users
    WHERE name = 'John' AND role = 'guest'`
);

// (b): Object-based syntax
const result = await client.database('public').table('users').select(
    { where: [
        { eq: ['name', { value: 'John' }] },
        { eq: ['role', { value: 'guest' }] }
    ] }
);

// (c): Function-based syntax
const result = await client.database('public').table('users').select(
    { where: [
        (q) => q.eq('name', (r) => r.value('John')),
        (q) => q.eq('role', (r) => r.value('guest'))
    ] }
);

Example 2:

Find records that satisfy a combination of static and dynamic conditions:

// (a): SQL syntax
const result = await client.query(
    `SELECT
        name,
        email
    FROM public.users
    WHERE role IS NOT NULL AND COALESCE(email, phone) IS NOT NULL`
);

// (b): Object-based syntax
const result = await client.database('public').table('users').select({
    fields: [ 'name', 'email' ],
    where: [
        { isNotNull: 'role' },
        { isNotNull: { fn: ['COALESCE', 'email', 'phone'] } }
    ]
});

// (c): Function-based syntax
const result = await client.database('public').table('users').select({
    fields: [ 'name', 'email' ],
    where: [
        { isNotNull: 'role' },
        { isNotNull: { fn: ['COALESCE', 'email', 'phone'] } }
    ]
});

Example 3:

Find records that satisfy a more complex set of conditions:

// (a): SQL syntax
const result = await client.query(
    `SELECT
        name,
        email
    FROM public.users
    WHERE (role = $1 OR role = $2) AND (
        email IS NOT NULL OR (
            phone IS NOT NULL AND country_code IS NOT NULL
        )
    )`
);

// (b): Object-based syntax
const result = await client.database('public').table('users').select({
    fields: [ 'name', 'email' ],
    where: [
        { some: [
            { eq: ['role', { binding: 'admin' }] },
            { eq: ['role', { binding: 'contributor' }] }
        ] },
        { some: [
            { isNotNull: 'email' },
            { every: [
                { isNotNull: 'phone' },
                { isNotNull: 'country_code' }
            ] }
        ] }
    ]
});

// (c): Function-based syntax
const result = await client.database('public').table('users').select({
    fields: [ 'name', 'email' ],
    where: [
        (q) => q.some(
            (r) => r.eq('role', (s) => s.binding('admin')),
            (r) => r.eq('role', (s) => s.binding('contributor')),
        ),
        (q) => q.some(
            (r) => r.isNotNull('email'),
            (r) => r.every(
                (s) => s.isNotNull('phone'),
                (s) => s.isNotNull('country_code')
            )
        )
    ]
});

The ORDER BY Clause

Use the ORDER BY clause to sort output rows by specific criteria.

Example 1:

Order output rows by a set of dynamic and static criteria:

// (a): SQL syntax
const result = await client.query(
    `SELECT
        name,
        email
    FROM public.users
    ORDER BY
        CASE role WHEN 'admin' THEN 1 WHEN 'contributor' THEN 2 ELSE 3 END ASC,
        CASE WHEN phone IS NULL THEN 0 ELSE 1 END DESC,
        name ASC`
);

// (b): Object-based syntax
const result = await client.database('public').table('users').select({
    fields: [ 'name', 'email' ],
    orderBy: [
        { expr: {
            switch: 'role',
            cases: [
                { when: { value: 'admin' }, then: 1 },
                { when: { value: 'contributor' }, then: 2 }
            ],
            default: 0
        }, asc: true },
        { expr: {
            cases: [ { when: { isNull: 'phone' }, then: 0 } ],
            default: 1
        }, desc: true },
        { expr: 'name', asc: true }
    ]
});

// (c): Function-based syntax
const result = await client.database('public').table('users').select({
    fields: [ 'name', 'email' ],
    orderBy: [
        (q) => q.expr(
            (r) => r.switch('role').cases(
                (s) => s.when((t) => t.value('admin')).then(1),
                (s) => s.when((t) => t.value('contributor')).then(2)
            ).default(3)
        ).asc(),
        (q) => q.expr(
            (r) => r.cases(
                (s) => s.when((t) => t.isNull('phone')).then(0)
            ).default(1)
        ).desc(),
        (q) => q.expr('name').asc()
    ]
});

The LIMIT Clause

Use the LIMIT clause to limit the number of rows returned by a query.

Example 1:

Retrieve the first 3 books from the books table:

// (a): SQL syntax
const result = await client.query(
    `SELECT
        title,
        author
    FROM public.books
    LIMIT 3`
);

// (b): Function-based syntax
const result = await client.database('public').table('books').select({
    fields: ['title', 'author'],
    limit: 3
});

The OFFSET Clause

Use the OFFSET clause to skip a specified number of rows before returning rows.

Example 1:

Extend the previous to retrieve the next 3 books after skipping the first 3:

// (a): SQL syntax
const result = await client.query(
    `SELECT
        title,
        author
    FROM public.books
    LIMIT 3 OFFSET 3`
);

// (b): Function-based syntax
const result = await client.database('public').table('books').select({
    fields: ['title', 'author'],
    limit: 3,
    offset: 3
});

The JOIN Clause

Using the JOIN clause, select records from one table and tie in relevamt records from related tables.

Example 1:

Return a list of books with an extra dimension each: the author's name from the users table:

// (a): SQL syntax
const result = await client.query(
    `SELECT
        books.title,
        books.content,
        usr.name AS author_name
    FROM public.books
    LEFT JOIN public.users AS usr ON books.author = usr.id
    WHERE usr.role = $1`,
    ['admin']
);

// (b): Object-based syntax
const result = await client.database('public').table('books').select(
    { fields: [
        [ 'books', 'title' ],
        [ 'books', 'content' ],
        { expr: [ 'usr', 'name' ], as: 'author_name' }
    ], leftJoin: {
        expr: ['public', 'users'], as: 'usr', on: [ 
            { eq: [['books', 'author'], ['usr', 'id'] ] } 
        ]
    }, where: [
        { eq: [ [ 'usr', 'role' ], { binding: ['admin'] } ] }
    ] }
);

// (c): Function-based syntax
const result = await client.database('public').table('books').select(
    { fields: [
        (q) => q.expr([ 'books', 'title' ]),
        (q) => q.expr([ 'books', 'content']),
        (q) => q.expr([ 'usr', 'name' ]).as('author_name'),
    ], leftJoin: (q) => q.expr(['public', 'users']).as('usr').on(
        (r) => r.eq(['books', 'author'], ['usr', 'id'])
    ), where: [
        (q) => q.eq(['usr', 'role'], (r) => r.binding('admin'))
    ] }
);

Example 2:

Extend the previous to include another related info: the co-author's name from the users table:

// (a): SQL syntax
const result = await client.query(
    `SELECT
        books.title,
        books.content,
        usr.name AS author_name,
        usr2.name AS coauthor_name
    FROM public.books
    LEFT JOIN public.users AS usr ON books.author = usr.id
    LEFT JOIN public.users AS usr2 ON books.coauthor = usr2.id
    WHERE usr.role = $1`,
    ['admin']
);

// (b): Object-based syntax
const result = await client.database('public').table('books').select(
    { fields: [
        [ 'books', 'title' ],
        [ 'books', 'content' ],
        { expr: [ 'usr', 'name' ], as: 'author_name' },
        { expr: [ 'usr2', 'name' ], as: 'coauthor_name' }
    ], joins: [
        { type: 'LEFT_JOIN', expr: ['public', 'users'], as: 'usr', on: [ 
            { eq: [['books', 'author'], ['usr', 'id'] ] } 
        ] },
        { type: 'LEFT_JOIN', expr: ['public', 'users'], as: 'usr2', on: [ 
            { eq: [['books', 'coauthor'], ['usr2', 'id'] ] } 
        ] }
    ], where: [
        { eq: [ [ 'usr', 'role' ], { binding: ['admin'] } ] }
    ] }
);

// (c): Function-based syntax
const result = await client.database('public').table('books').select(
    { fields: [
        (q) => q.expr([ 'books', 'title' ]),
        (q) => q.expr([ 'books', 'content']),
        (q) => q.expr([ 'usr', 'name' ]).as('author_name'),
    ], joins: [
        (q) => q.type('LEFT_JOIN').expr(['public', 'users']).as('usr').on(
            (r) => r.eq(['books', 'author'], ['usr', 'id'])
        ),
        (q) => q.type('LEFT_JOIN').expr(['public', 'users']).as('usr2').on(
            (r) => r.eq(['books', 'coauthor'], ['usr2', 'id'])
        )
    ], where: [
        (q) => q.eq(['usr', 'role'], (r) => r.binding('admin'))
    ] }
);

The GROUP BY Clause

Use the GROUP BY clause to group rows together by specific criteria for use with aggregate functions.

Example 2:

Count the number of books written by each author:

// (a): SQL syntax
const result = await client.query(
    `SELECT author, COUNT(*) AS total_books
    FROM public.books
    GROUP BY author`
);

// (b): Object-based syntax
const result = await client.database('public').table('books').select(
    { fields: [
        { expr: 'author' },
        { expr: {
            fn: ['COUNT', '*']
        }, as: 'total_books' }
    ], groupBy: ['author'] }
);

// (c): Function-based syntax
const result = await client.database('public').table('books').select(
    { fields: [
        (q) => q.expr('author'),
        (q) => q.expr(
            (r) => r.fn('COUNT', '*')
        ).as('total_books')
    ], groupBy: ['author'] }
);

The HAVING Clause

Use the HAVING clause to filter groups based on the result of aggregate functions.

Example 1:

Limit the results of the previous to only authors with more than 5 books:

// (a): SQL syntax
const result = await client.query(
    `SELECT author, COUNT(*) AS total_books
    FROM public.books
    GROUP BY author
    HAVING COUNT(*) > 5`
);

// (b): Object-based syntax
const result = await client.database('public').table('books').select(
    { fields: [
        { expr: 'author' },
        { expr: {
            fn: ['COUNT', '*']
        }, as: 'total_books' }
    ], groupBy: ['author'], having: [
        { gt: [{ fn: ['COUNT', '*'] }, 4] }
    ] }
);

// (c): Function-based syntax
const result = await client.database('public').table('books').select(
    { fields: [
        (q) => q.expr('author'),
        (q) => q.expr(
            (r) => r.fn('COUNT', '*')
        ).as('total_books')
    ], groupBy: ['author'], having: [
        (q) => q.gt((r) => r.fn('COUNT', '*'), 4)
    ] }
);

The WINDOW Clause

Using the WINDOW clause, define reusable window specifications for window functions, enabling consistent and concise definitions across multiple functions in a query.

Example 1:

Do a little statistics on the books table: for each book, get the daily engagement score on the day book was published:

// (a): SQL syntax
const result = await client.query(
    `SELECT
        title,
        content,
        RANK(num_views ORDER BY content ASC) OVER window_1 AS engagement_score,
    FROM public.books
    WINDOW window_1 AS (
        PARTITION BY created_at
        ORDER BY title ASC
    )`
);

// (b): Object-based syntax
const result = await client.database('public').table('books').select(
    { fields: [
        { expr: 'title' },
        { expr: 'content' },
        { expr: {
            fn: ['RANK', 'num_views'], over: 'window_1', orderBy: { expr: 'content', asc: true }
        }, as: 'engagement_score' }
    ], window: [
        { name: 'window_1', partitionBy: 'created_at', orderBy: [
            { expr: 'title', asc: true }
        ] }
    ] }
);

// (c): Function-based syntax
const result = await client.database('public').table('books').select(
    { fields: [
        (q) => q.expr('title'),
        (q) => q.expr('content'),
        (q) => q.expr(
            (r) => r.fn('RANK', 'num_views').over('window_1').orderBy((s) => s.expr('content').asc())
        ).as('engagement_score')
    ], window: [
        (q) => q.name('window_1').partitionBy('created_at').orderBy(
            (r) => r.expr('title').asc()
        )
    ] }
);

Structuring Syntax

While you could stitch relevant SQL functions together to structure your output data, Linked QL supports special JSON-like syntaxes that abstract the rough work. (See ➞ JSON Sugars.)

Example 1:

Return output fields in specific formats:

// (a): SQL syntax
const result = await client.query(
    `SELECT
        name,
        phone,
        { email, phone AS mobile } AS contact1,
        [ email, phone ] AS contact2
    FROM public.users`
);

// (b): Object-based syntax
const result = await client.database('public').table('users').select({
    fields: [
        { expr: 'name' },
        { expr: 'phone' },
        { expr: {
            fields: ['email', { expr: 'phone', as: 'mobile'}]
        }, as: 'contact1' },
        { expr: {
            items: ['email', 'phone']
        }, as: 'contact2' }
    ]
});

// (c): Function-based syntax
const result = await client.database('public').table('users').select({
    fields: [
        (q) => q.expr('name'),
        (q) => q.expr('phone'),
        (q) => q.expr(
            (r) => r.fields('email', (s) => s.expr('phone').as('mobile'))
        ).as('contact1'),
        (q) => q.expr(
            (r) => r.items('email', 'phone')
        ).as('contact2')
    ]
});

Magic Paths

While you could use the traditional JOIN approach to query relational data, Linked QL supports special path operators that let you express relationships graphically. (See ➞ Magic Paths.)

Example 1:

Return a list of books with an extra dimension each: the author's name from the users table:

// (a): SQL syntax
const result = await client.query(
    `SELECT
        title,
        content,
        author ~> name AS author_name
    FROM public.books
    WHERE author ~> role = $1`,
    ['admin']
);

// (b): Object-based syntax
const result = await client.database('public').table('books').select(
    { fields: [
        { expr: 'title' },
        { expr: 'content' },
        { expr: {
            path: ['author', '~>', 'name']
        }, as: 'author_name' }
    ], where: [
        { eq: [
            { path: ['author', '~>', 'role'] },
            { binding: ['admin'] }
        ] }
    ] }
);

// (c): Function-based syntax
const result = await client.database('public').table('books').select(
    { fields: [
        (q) => q.expr('title'),
        (q) => q.expr('content'),
        (q) => q.expr(
            (r) => r.path('author', '~>', 'name')
        ).as('author_name')
    ], where: [
        (q) => q.eq(
            (r) => r.path('author', '~>', 'role'),
            (r) => r.binding('admin')
        )
    ] }
);

Example 2:

Extend the previous to include another related info: the co-author's name from the users table:

// (a): SQL syntax
const result = await client.query(
    `SELECT
        title,
        content,
        author ~> name AS author_name,
        coauthor ~> name AS coauthor_name
    FROM public.books
    WHERE author ~> role = $1`,
    ['admin']
);

// (b): Object-based syntax
const result = await client.database('public').table('books').select(
    { fields: [
        { expr: 'title' },
        { expr: 'content' },
        { expr: {
            path: ['author', '~>', 'name']
        }, as: 'author_name' },
        { expr: {
            path: ['coauthor', '~>', 'name']
        }, as: 'coauthor_name' }
    ], where: [
        { eq: [
            { path: ['author', '~>', 'role'] },
            { binding: ['admin'] }
        ] }
    ] }
);

// (c): Function-based syntax
const result = await client.database('public').table('books').select(
    { fields: [
        (q) => q.expr('title'),
        (q) => q.expr('content'),
        (q) => q.expr(
            (r) => r.path('author', '~>', 'name')
        ).as('author_name'),
        (q) => q.expr(
            (r) => r.path('coauthor', '~>', 'name')
        ).as('coauthor_name')
    ], where: [
        (q) => q.eq(
            (r) => r.path('author', '~>', 'role'),
            (r) => r.binding('admin')
        )
    ] }
);

Example 3:

Return a list of books with an extra dimension each: details of the author:

// (a): SQL syntax
const result = await client.query(
    `SELECT
        title,
        content,
        author: { name, email } AS author
    FROM public.books
    WHERE author ~> role = $1`,
    ['admin']
);

// (b): Object-based syntax
const result = await client.database('public').table('books').select(
    { fields: [
        { expr: 'title' },
        { expr: 'content' },
        { expr: {
            rpath: ['author', { fields: ['name', 'email'] }]
        }, as: 'author' }
    ], where: [
        { eq: [
            { path: ['author', '~>', 'role'] },
            { binding: ['admin'] }
        ] }
    ] }
);

// (c): Function-based syntax
const result = await client.database('public').table('books').select(
    { fields: [
        (q) => q.expr('title'),
        (q) => q.expr('content'),
        (q) => q.expr(
            (r) => r.rpath('author', (s) => s.fields('name', 'email'))
        ).as('author'),
    ], where: [
        (q) => q.eq(
            (r) => r.path('author', '~>', 'role'),
            (r) => r.binding('admin')
        )
    ] }
);