Skip to content

SELECT

Jump to bottom Edit New page
Oxford Harrison edited this page Nov 17, 2024 · 16 revisions

DOCS • LANG

The SELECT statement.

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

Jump to...

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

The WHERE clause in SQL is used 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(
    [ 'name', 'email' ],
    { where: [
        { isNotNull: 'role' },
        { isNotNull: { fn: ['COALESCE', 'email', 'phone'] } }
    ] }
);
// (c): Function-based syntax
const result = await client.database('public').table('users').select(
    [ '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(
    [ '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(
    [ '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

The ORDER BY clause in SQL is used 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(
    [ '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(
    [ '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

The SQL LIMIT clause lets you restrict the number of rows returned by a query to a specified count.

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(
    ['title', 'author'],
    { limit: 3 }
);

The OFFSET Clause

The SQL OFFSET clause lets you 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(
    ['title', 'author'],
    { limit: 3, offset: 3 }
);

The JOIN Clause

With the JOIN clause, SQL allows you to 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

The SQL GROUP BY clause lets you group rows together by specified 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

The HAVING clause lets you 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

With the WINDOW clause, SQL allows you to 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,
        author: {name, email} AS author
    FROM public.books
    WHERE author ~> role IS NOT NULL 
    WINDOW window_1 AS (
        PARTITION BY created_at
        ORDER BY
            CASE author ~> role WHEN 'admin' THEN 1 WHEN 'contributor' THEN 2 ELSE 0 END ASC,
            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' },
        { expr: {
            rpath: ['author', { fields: ['name', 'email'] }]
        }, as: 'author' }
    ], where: [
        { isNotNull: [
            { path: ['author', '~>', 'role'] }
        ] }
    ], window: [
        { name: 'window_1', partitionBy: 'created_at', orderBy: [
            { expr: {
                switch: { rpath: ['author', 'role'] }, cases: [
                    { when: { value: 'admin' }, then: 1 },
                    { when: { value: 'contributor' }, then: 2 }
                ], default: 0
            }, asc: true },
            { 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'),
        (q) => q.expr(
            (r) => r.rpath('author', (s) => s.fields('name', 'email'))
        ).as('author')
    ], where: [
        (q) => q.isNotNull(
            (r) => r.path('author', '~>', 'role')
        )
    ], window: [
        (q) => q.name('window_1').partitionBy('created_at').orderBy(
            (r) => r.expr(
                (s) => s.switch((t) => t.rpath('author', 'role')).cases(
                    (t) => t.when((u) => u.value('admin')).then(1),
                    (t) => t.when((u) => u.value('contributor')).then(2),
                ).default(0)
            ).asc(),
            (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([
    { 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([
    (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')
        )
    ] }
);
Add a custom footer
Add a custom sidebar
Clone this wiki locally