Selecting Data from a Table

To select some rows from a table, use a standard SELECT query:

SELECT * from myTable WHERE column1 = 'a value'

To select specific columns from the table, just replace the asterisk with the column names:

SELECT column2, column3, column5 FROM myTable WHERE column2 LIKE 'goat%'

Data which is produced from a SELECT query is returned as a set of comma-delimited values, with the first row containing the column names.
Given the aforementioned SELECT query, the results would look something like this:

goat,this data,another column
goats,that data,a second column
goatee,their data,the last column
     3 Rows.

BearDB supports all the SQL comparators in a WHERE CLAUSE:

    equals '='
    not equals '<>'
    greater than '>'
    less than '<'
    greater than or equal '>='
    less than or equal '<='

BearDB currently has one limitation when using the SQL WHERE clause:

  • The WHERE CLAUSE in a SQL statement does not support the use of parens in this release. As a result, the use of AND and OR in a WHERE clause will be evaluated from left to right.

If it is necessary to SELECT rows with a NULL value you can do this by specifying the column value as '' in the WHERE clause. (two single quotes with no space inbetween)

To control the ordering of one of the returned columns, add the ORDER BY clause to the end of the SELECT query:

SELECT * FROM myTable WHERE column1 = 'value' ORDER BY column2 ASC

The ORDER BY clause will order content Descending by default. You can add a value to the ORDER BY clause to control the ordering. Use ORDER BY column2 DESC to sort Descending, and ORDER BY column2 ASC to sort Ascending.

Using subselects

Select queries can be nested as subselects using the WHERE [value] IN clause:

SELECT * from myTable WHERE column1 IN (SELECT column1 from otherTable where column3 < 45)

Subselects have the following required properties:

  • Subselects only work with a SELECT query. Subselects cannot be used in the WHERE clause of an INSERT, UPDATE, or DELETE query in this release of BearDB.
  • The subselect SELECT query must return a column of the same name as the parent select query, as does column1 in the above example. This also means that a subselect SELECT query can SELECT * as long as the column in the parent SELECT query exists.
  • Surround the subselect SELECT query in a set of parenthensis.
    Subselect queries can be nested into a query hierarchy. A subselect can contain one or more of its own subselect SELECT queries.
  • The SELECT clause in a subselect can have all the same SQL commands as in a single SELECT clause.
    Subselect queries do not allow the comparison of IN SET MATCHING in this release of BearDB. (ex: WHERE col3 IN (41, 53, 61))