# From

Queries come in many varieties—from the basic to extremely complex. In order to provide you maximum flexibility there are several ways to define the source table for you query.

## Using the `from()` method

The most common method for defining the source table is using the `from()` method. For the majority of queries, the `from()` method is all you need. It's syntax is very easy:

```javascript
//qb
var getResults = query.from('users').get();

//sql
SELECT * FROM `users`
```

This would return all columns from the `users` table.

> *NOTE:* Alternatively, you can use the `table()` method as an alias to `from()`.

### Declaring a table alias

Optionally you can specify an alias for the table by using the syntax:

```javascript
//qb
var getResults = query.from('users as u').get();

//sql
SELECT * FROM `users` AS `u`
```

This would parse the string `users as u` and convert it into the correct syntax for current grammar.

Alternatively, you can use the ANSI SQL shorthand and leave out the `as` keyword:

```javascript
//qb
var getResults = query.from('users u').get();

//sql
SELECT * FROM `users` AS `u`
```

## Defining the `from` clause using raw SQL

Sometimes you need more control over your `from` clause in order to add grammar specific instructions, such as adding SQL Server table hints to your queries.

If you need complete control over your `from` clause you can use the `fromRaw()`.

For example, to provide a table hint for a SQL Server query you could use:

```javascript
//qb
var getResults = query.fromRaw('[users] u (nolock)').get();

//sql
SELECT * FROM [users] u (nolock)
```

Since the `fromRaw()` takes your string verbatim, it's important that you make sure your SQL declaration is escaped properly. Failure to properly escape your table names may result in SQL errors.

> *NOTE:* Using the `fromRaw()` will most likely tie your code to a specific database, so think carefully before using the `fromRaw()` method if you want your project to be database agnostic.

### Adding bindings to your raw `from` clause

Many database engines allow you to define User Defined Functions. For example, SQL Server allows you to define UDFs that will return a table. In these type of cases, it may be necessary to bind parameters to your `from` clause.

You can bind parameters to the `fromRaw()` method by passing a secondary argument that is an array of the parameters to bind:

```javascript
//qb
var getResults = query
    .fromRaw('dbo.generateDateTable(?, ?, ?) as dt', ['2017-01-01', '2017-12-31', 'm'])
    .get()
;

//sql
SELECT * FROM dbo.generateDateTable('2017-01-01', '2017-12-31', 'm') as dt
```

## Derived tables

Complex queries often contain derived tables. Derived tables are essentially a temporal table defined as a subquery in the `from` statement.

You can build queries that comprise of derived tables by using the `fromSub()` method, which requires two arguments:

* The alias to use for the derived table (which is how you reference your query)
* Either a QueryBuilder instances or closure defining the subquery

### Defining a derived table using a closure

The simplest way to create a derived table is by using a closure to define the subquery:

```javascript
//qb
var getResults = query
    .select('firstName', 'lastName')
    .fromSub('u', function (q){
        q
            .select('lName as lastName', 'fName as firstName')
            .from('users')
            .where('age', '>=', 21)
        ;
    })
    .orderBy('lastName')
    .get()
;

//sql
SELECT `firstName`, `lastName` FROM (SELECT `lName` as `lastName`, `fName` as `firstName` FROM `users` WHERE `age` >= 21) AS `u` ORDER BY `lastName`
```

### Defining a derived table using a QueryBuilder instance

Alternatively you can supply a QueryBuilder instance to the `fromSub()` method:

```javascript
//qb
var derivedQA = query
    .select('lName as lastName', 'fName as firstName')
    .from('users')
    .where('age', '>=', 21)
;

var getResults = query
    .select('firstName', 'lastName')
    .fromSub('u', derivedQA)
    .orderBy('lastName')
    .get()
;

//sql
SELECT `firstName`, `lastName` FROM (SELECT `lName` as `lastName`, `fName` as `firstName` FROM `users` WHERE `age` >= 21) AS `u` ORDER BY `lastName`
```