### Install and Generate with Optgen

Source: https://github.com/dolthub/go-mysql-server/blob/main/optgen/README.md

Install the optgen command-line tool and then run go generate to produce analyzer code. This is the primary way to integrate optgen into your project.

```bash
go install ./optgen/cmd/optgen
```

```bash
go generate ./...
```

--------------------------------

### Start In-Memory MySQL Server in Go

Source: https://github.com/dolthub/go-mysql-server/blob/main/README.md

This Go code sets up and starts an in-memory MySQL server. It includes creating a database provider, engine, and server configuration. Ensure necessary imports are present. The server can be configured with custom protocols and addresses.

```go
package main

import (
	"context"
	"fmt"
	"time"

	"github.com/dolthub/vitess/go/vt/proto/query"

	sqle "github.com/dolthub/go-mysql-server"
	"github.com/dolthub/go-mysql-server/memory"
	"github.com/dolthub/go-mysql-server/server"
	"github.com/dolthub/go-mysql-server/sql"
	"github.com/dolthub/go-mysql-server/sql/types"
)

// This is an example of how to implement a MySQL server.
// After running the example, you may connect to it using the following:
//
// > mysql --host=localhost --port=3306 --user=root mydb --execute="SELECT * FROM mytable;"
// +----------+-------------------+-------------------------------+----------------------------+
// | name     | email             | phone_numbers                 | created_at                 |
// +----------+-------------------+-------------------------------+----------------------------+
// | Jane Deo | janedeo@gmail.com | ["556-565-566","777-777-777"] | 2022-11-01 12:00:00.000001 |
// | Jane Doe | jane@doe.com      | []                            | 2022-11-01 12:00:00.000001 |
// | John Doe | john@doe.com      | ["555-555-555"]               | 2022-11-01 12:00:00.000001 |
// | John Doe | johnalt@doe.com   | []                            | 2022-11-01 12:00:00.000001 |
// +----------+-------------------+-------------------------------+----------------------------+
//
// The included MySQL client is used in this example, however any MySQL-compatible client will work.

var (
	dbName    = "mydb"
	tableName = "mytable"
	address   = "localhost"
	port      = 3306
)

func main() {
	pro := createTestDatabase()
	engine := sqle.NewDefault(pro)

	session := memory.NewSession(sql.NewBaseSession(), pro)
	ctx := sql.NewContext(context.Background(), sql.WithSession(session))
	ctx.SetCurrentDatabase(dbName)

	// This variable may be found in the "users_example.go" file. Please refer to that file for a walkthrough on how to
	// set up the "mysql" database to allow user creation and user checking when establishing connections. This is set
	// to false for this example, but feel free to play around with it and see how it works.
	if enableUsers {
		if err := enableUserAccounts(ctx, engine); err != nil {
			panic(err)
		}
	}

	config := server.Config{
		Protocol: "tcp",
		Address:  fmt.Sprintf("%s:%d", address, port),
	}
	s, err := server.NewServer(config, engine, sql.NewContext, memory.NewSessionBuilder(pro), nil)
	if err != nil {
		panic(err)
	}
	if err = s.Start(); err != nil {
		panic(err)
	}
}

func createTestDatabase() *memory.DbProvider {
	db := memory.NewDatabase(dbName)

	pro := memory.NewDBProvider(db)
	session := memory.NewSession(sql.NewBaseSession(), pro)
	ctx := sql.NewContext(context.Background(), sql.WithSession(session))

	table := memory.NewTable(ctx, db, tableName, sql.NewPrimaryKeySchema(sql.Schema{
		{Name: "name", Type: types.Text, Nullable: false, Source: tableName, PrimaryKey: true},
		{Name: "email", Type: types.Text, Nullable: false, Source: tableName, PrimaryKey: true},
		{Name: "phone_numbers", Type: types.JSON, Nullable: false, Source: tableName},
		{Name: "created_at", Type: types.MustCreateDatetimeType(query.Type_DATETIME, 6), Nullable: false, Source: tableName},
	}), db.GetForeignKeyCollection())
	db.AddTable(tableName, table)

	creationTime := time.Unix(0, 1667304000000001000).UTC()
	_ = table.Insert(ctx, sql.NewRow("Jane Deo", "janedeo@gmail.com", types.MustJSON(`["556-565-566", "777-777-777"]`), creationTime))
	_ = table.Insert(ctx, sql.NewRow("Jane Doe", "jane@doe.com", types.MustJSON(`[]`), creationTime))
	_ = table.Insert(ctx, sql.NewRow("John Doe", "john@doe.com", types.MustJSON(`["555-555-555"]`), creationTime))
	_ = table.Insert(ctx, sql.NewRow("John Doe", "johnalt@doe.com", types.MustJSON(`[]`), creationTime))

	return pro
}

```

--------------------------------

### Connect to In-Memory MySQL Server

Source: https://github.com/dolthub/go-mysql-server/blob/main/README.md

After starting the in-memory server, you can connect to it using any MySQL-compatible client. This example shows how to query data from the 'mytable' using the 'mysql' command-line client.

```bash
> mysql --host=localhost --port=3306 --user=root mydb --execute="SELECT * FROM mytable;"
+----------+-------------------+-------------------------------+----------------------------+
| name     | email             | phone_numbers                 | created_at                 |
+----------+-------------------+-------------------------------+----------------------------+
| Jane Deo | janedeo@gmail.com | ["556-565-566","777-777-777"] | 2022-11-01 12:00:00.000001 |
| Jane Doe | jane@doe.com      | []                            | 2022-11-01 12:00:00.000001 |
| John Doe | john@doe.com      | ["555-555-555"]               | 2022-11-01 12:00:00.000001 |

```

--------------------------------

### Install go-mysql-server

Source: https://github.com/dolthub/go-mysql-server/blob/main/README.md

Add go-mysql-server as a dependency to your Go project. Ensure you have a C/C++ toolchain and libicu-dev installed if you need ICU-compatible regexes. For a pure Go regex implementation, compile with -tags=gms_pure_go, though this is not recommended for full MySQL compatibility.

```bash
go get github.com/dolthub/go-mysql-server@latest
```

--------------------------------

### Example SQL Query Plan

Source: https://github.com/dolthub/go-mysql-server/blob/main/ARCHITECTURE.md

Illustrates a simple SQL query translated into an execution plan tree structure. Each node implements the `sql.Node` interface.

```go
Project(foo)
 |- Table(bar)
```

--------------------------------

### Connect to MySQL Server using C Connector

Source: https://github.com/dolthub/go-mysql-server/blob/main/SUPPORTED_CLIENTS.md

Demonstrates how to connect to a MySQL server, execute a query, and fetch results using the C Connector. Ensure the MySQL C Connector library is installed and linked.

```c
#include <my_global.h>
#include <mysql.h>

void finish_with_error(MYSQL *con)
{
    fprintf(stderr, "%s\n", mysql_error(con));
    mysql_close(con);
    exit(1);
}

int main(int argc, char **argv)
{
    MYSQL *con = NULL;
    MYSQL_RES *result = NULL;
    int num_fields = 0;
    MYSQL_ROW row;

    printf("MySQL client version: %s\n", mysql_get_client_info());

    con = mysql_init(NULL);
    if (con == NULL) {
        finish_with_error(con);
    }

    if (mysql_real_connect(con, "127.0.0.1", "root", "", "mydb", 3306, NULL, 0) == NULL) {
        finish_with_error(con);
    }

    if (mysql_query(con, "SELECT name, email, phone_numbers FROM mytable")) {
        finish_with_error(con);
    }

    result = mysql_store_result(con);
    if (result == NULL) {
        finish_with_error(con);
    }

    num_fields = mysql_num_fields(result);
    while ((row = mysql_fetch_row(result))) {
        for(int i = 0; i < num_fields; i++) {
            printf("%s ", row[i] ? row[i] : "NULL");
        }
        printf("\n");
    }

    mysql_free_result(result);
    mysql_close(con);

    return 0;
}
```

--------------------------------

### Run Integration Tests

Source: https://github.com/dolthub/go-mysql-server/blob/main/ARCHITECTURE.md

Command to run integration tests for a specific client. It automates the setup and teardown of the test server.

```bash
make TEST=${CLIENT FOLDER NAME} integration
```

--------------------------------

### Left Outer Join with Table Setup

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Uses the previously created tables 'a' and 'b' to perform a left outer join, ordering the results.

```sql
SELECT * FROM a LEFT OUTER JOIN b ON a.i = b.i ORDER BY a.i, b.i, b.b
```

--------------------------------

### Natural Join Example

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Demonstrates a natural join between two tables. Ensure tables have common columns for natural join to work.

```sql
SELECT * FROM onecolumn NATURAL JOIN twocolumn
```

--------------------------------

### Right Outer Join with Table Setup

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Performs a right outer join on tables 'a' and 'b', demonstrating how rows from the right table are prioritized.

```sql
SELECT * FROM a RIGHT OUTER JOIN b ON a.i = b.i ORDER BY a.i, b.i, b.b
```

--------------------------------

### Create Customer Table

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Defines a 'c' table with customer ID and billing state. Used for correlated subquery examples.

```sql
CREATE TABLE c (c_id INT PRIMARY KEY, bill TEXT);
```

--------------------------------

### Selecting Aliased Columns from Multiple Joins

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Expands on the previous example by selecting aliased columns from each instance of the joined table, ordered by the aliased column.

```sql
SELECT a.x AS s, b.x, c.x, a.y, b.y, c.y FROM (twocolumn AS a JOIN twocolumn AS b USING(x) JOIN twocolumn AS c USING(x)) ORDER BY s
```

--------------------------------

### Create Orders Table

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Defines an 'o' table with order ID, customer ID, and shipping state. Used for correlated subquery examples.

```sql
CREATE TABLE o (o_id INT PRIMARY KEY, c_id INT, ship TEXT);
```

--------------------------------

### INNER JOIN from Empty Table using USING

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Performs an INNER JOIN starting with an empty table and 'onecolumn' using the USING clause. The result is empty because the 'empty' table has no rows.

```sql
query I nosort
SELECT * FROM `empty` AS a JOIN onecolumn AS b USING(x)
----
```

--------------------------------

### LEFT OUTER JOIN from Empty Table using USING

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Performs a LEFT OUTER JOIN starting with an empty table and 'onecolumn' using the USING clause. The result is empty because the left table has no rows.

```sql
query I nosort
SELECT * FROM `empty` AS a LEFT OUTER JOIN onecolumn AS b USING(x)
----
```

--------------------------------

### Generate Series with EXCEPT ALL

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

This query demonstrates the use of `generate_series` in conjunction with `EXCEPT ALL` to produce a sequence of numbers. It selects numbers starting from `a + 1` where `a` is derived from a set difference operation.

```sql
SELECT
    generate_series(a + 1, a + 1)
FROM
    (SELECT a FROM ((SELECT 1 AS a, 1) EXCEPT ALL (SELECT 0, 0)))
```

--------------------------------

### CROSS JOIN from Empty Table

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Performs a CROSS JOIN starting with an empty table and 'onecolumn'. The result set is empty because the 'empty' table has no rows.

```sql
query II nosort
SELECT * FROM `empty` AS a CROSS JOIN onecolumn AS b
----
```

--------------------------------

### LEFT OUTER JOIN from Empty Table

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Shows a LEFT OUTER JOIN starting with an empty table and 'onecolumn'. The result is empty because the left table has no rows.

```sql
query II nosort
SELECT a.x AS x, b.x AS y FROM `empty` AS a LEFT OUTER JOIN onecolumn AS b ON a.x = b.x
----
```

--------------------------------

### INNER JOIN from Empty Table

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Executes an INNER JOIN starting with an empty table and 'onecolumn' using an ON condition. The result is empty as the 'empty' table provides no rows to join.

```sql
query II nosort
SELECT a.x AS x, b.x AS y FROM `empty` AS a JOIN onecolumn AS b ON a.x = b.x
----
```

--------------------------------

### Using the in-memory test server

Source: https://github.com/dolthub/go-mysql-server/blob/main/README.md

This section demonstrates how to set up and use the in-memory test server for testing purposes. It's useful for simulating a MySQL server environment without external dependencies.

```go
package main

import (
	"context"
	"fmt"
	"github.com/dolthub/go-mysql-server/auth"
	"github.com/dolthub/go-mysql-server/memory"
	"github.com/dolthub/go-mysql-server/server"
	"github.com/dolthub/go-mysql-server/sql"
	"github.com/dolthub/go-mysql-server/sql/types"
)

func main() {
	// Define schema for the database
	dbSchema := memory.NewDatabaseSchema(map[string]*memory.TableSchema{
		"users": {
			Columns: []memory.Column{
				{Name: "id", Type: types.Int64, PrimaryKey: true},
				{Name: "name", Type: types.Text},
			},
		},
	})

	// Create an in-memory database engine
	engine := memory.NewEngine(dbSchema)

	// Create a new MySQL server instance
	config := server.NewDefaultConfig(auth.NewNativeSingle "root", "password", sql.NewMemoryDatabaseProvider(engine))
	ctx := context.Background()
	server, err := server.NewDefaultServer(config)
	if err != nil {
		panic(err)
	}
	defer server.Close()

	// Start the server
	go server.Start() // Run in a goroutine to avoid blocking

	// You can now connect to this server using a MySQL client
	// For example, using the go-mysql-driver:
	// dsn := "root:password@tcp(127.0.0.1:3306)/?parseTime=true"
	// db, err := sql.Open("mysql", dsn)
	// if err != nil {
	// 	panic(err)
	// }
	// defer db.Close()

	// Example query (this part would typically be in a test or separate function)
	query := "SELECT * FROM users"
	rows, err := engine.Query(ctx, query)
	if err != nil {
		fmt.Printf("Error executing query: %v\n", err)
	} else {
		for _, row := range rows {
			fmt.Println(row)
		}
	}
}

```

--------------------------------

### Create and Insert into 'onecolumn_w' Table

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Sets up a table named 'onecolumn_w' with a single integer column 'w' and populates it with values.

```sql
statement ok
CREATE TABLE onecolumn_w(w INT)

statement ok
INSERT INTO onecolumn_w(w) VALUES (42),(43)
```

--------------------------------

### Query with go-sql-driver/mysql

Source: https://github.com/dolthub/go-mysql-server/blob/main/SUPPORTED_CLIENTS.md

Use the go-sql-driver/mysql package to open a database connection and execute a query. Handle potential errors during connection and query execution.

```go
package main

import (
	"database/sql"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
    db, err := sql.Open("mysql", "root:@tcp(127.0.0.1:3306)/mydb")
	if err != nil {
		// handle error
	}

	rows, err := db.Query("SELECT * FROM mytable LIMIT 1")
	if err != nil {
		// handle error
    }

    // use rows
}
```

--------------------------------

### Create and Insert into 'twocolumn' Table

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Sets up a table named 'twocolumn' with two integer columns, 'x' and 'y', and populates it with sample data including a NULL value.

```sql
statement ok
CREATE TABLE twocolumn (x INT, y INT)

statement ok
INSERT INTO twocolumn(x, y) VALUES (44,51), (NULL,52), (42,53), (45,45)
```

--------------------------------

### Create and Insert into 'othercolumn' Table

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Sets up a table named 'othercolumn' with a single integer column 'x' and populates it with values.

```sql
statement ok
CREATE TABLE othercolumn (x INT)

statement ok
INSERT INTO othercolumn(x) VALUES (43),(42),(16)
```

--------------------------------

### Left Outer Join with Different Filter

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Another example of a left outer join with a filter condition on the left table's column.

```sql
SELECT o.x, t.y FROM onecolumn o LEFT OUTER JOIN twocolumn t ON (o.x=t.x AND o.x=44)
```

--------------------------------

### Create and Insert into 'onecolumn' Table

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Sets up a table named 'onecolumn' with a single integer column 'x' and populates it with values including NULL.

```sql
statement ok
CREATE TABLE onecolumn (x INT)

statement ok
INSERT INTO onecolumn(x) VALUES (44), (NULL), (42)
```

--------------------------------

### Connect and Query with Python mysql-connector

Source: https://github.com/dolthub/go-mysql-server/blob/main/SUPPORTED_CLIENTS.md

Connect to the database using the mysql-connector library and execute a query. Results are fetched using fetchall(). The connection should be closed.

```python
import mysql.connector

connection = mysql.connector.connect(host='127.0.0.1',
                                user='root',
                                passwd='',
                                port=3306,
                                database='mydb')

try:
    cursor = connection.cursor()
    sql = "SELECT * FROM mytable LIMIT 1"
    cursor.execute(sql)
    rows = cursor.fetchall()

    # use rows
finally:
    connection.close()
```

--------------------------------

### Connect and Query with pymysql

Source: https://github.com/dolthub/go-mysql-server/blob/main/SUPPORTED_CLIENTS.md

Use pymysql to establish a connection and execute a query, returning results as dictionaries. Ensure the connection is closed in a finally block.

```python
import pymysql.cursors

connection = pymysql.connect(host='127.0.0.1',
                             user='root',
                             password='',
                             db='mydb',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:
        sql = "SELECT * FROM mytable LIMIT 1"
        cursor.execute(sql)
        rows = cursor.fetchall()

        # use rows
finally:
    connection.close()
```

--------------------------------

### Check SQLLogicTest Files

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/README.md

Run this command to execute SQLLogicTests against a running MySQL or Dolt server. The output can help identify and fix query issues, such as differences in syntax or missing ORDER BY clauses.

```shell
go run ./check/check.go <infile>
```

--------------------------------

### Create Stuff Table

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Creates a 'stuff' table with an integer ID, a DATE, and a user_id referencing the 'users' table. This table stores items associated with users.

```sql
CREATE TABLE stuff (
    id INT8 NOT NULL,
    date DATE,
    user_id INT8,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users (id)
);
```

--------------------------------

### Connect and Query with ruby-mysql

Source: https://github.com/dolthub/go-mysql-server/blob/main/SUPPORTED_CLIENTS.md

Establish a connection using the ruby-mysql gem and execute a query. The response is processed, and the connection is closed.

```ruby
require "mysql"

conn = Mysql::new("127.0.0.1", "root", "", "mydb")
resp = conn.query "SELECT * FROM mytable LIMIT 1"

# use resp

conn.close()
```

--------------------------------

### Create Table pkBC

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Creates a table named 'pkBC' with columns a, b, c, d and a primary key on (b, c).

```sql
CREATE TABLE pkBC (a INT, b INT, c INT, d INT, PRIMARY KEY(b,c))
```

--------------------------------

### Select Customers with Matching Bill and Ship Addresses using IN

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Finds customers whose billing state matches the shipping state of at least one of their orders.

```sql
SELECT * FROM c WHERE bill IN (SELECT ship FROM o WHERE o.c_id=c.c_id);
```

--------------------------------

### Connect and Query with mysqljs

Source: https://github.com/dolthub/go-mysql-server/blob/main/SUPPORTED_CLIENTS.md

Connect to MySQL using the mysqljs library, execute a query, and handle potential errors. The connection is then ended.

```javascript
import mysql from 'mysql';

const connection = mysql.createConnection({
    host: '127.0.0.1',
    port: 3306,
    user: 'root',
    password: '',
    database: 'mydb'
});
connection.connect();

const query = 'SELECT * FROM mytable LIMIT 1';
connection.query(query, function (error, results, _) {
    if (error) throw error;

    // use results
});

connection.end();
```

--------------------------------

### Create Table pkBA

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Creates a table named 'pkBA' with columns a, b, c, d and a primary key on (b, a).

```sql
CREATE TABLE pkBA (a INT, b INT, c INT, d INT, PRIMARY KEY(b,a))
```

--------------------------------

### Create Table pkBAD

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Creates a table named 'pkBAD' with columns a, b, c, d and a primary key on (b, a, d).

```sql
CREATE TABLE pkBAD (a INT, b INT, c INT, d INT, PRIMARY KEY(b,a,d))
```

--------------------------------

### Select Customers with All Orders Matching Bill and Ship Addresses using ALL

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Retrieves customers where their billing state matches the shipping state for ALL of their orders.

```sql
SELECT * FROM c WHERE bill = ALL(SELECT ship FROM o WHERE o.c_id=c.c_id);
```

--------------------------------

### Insert Customer Data

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Populates the 'c' table with sample customer data, including NULL billing states.

```sql
INSERT INTO c VALUES
    (1, 'CA'),
    (2, 'TX'),
    (3, 'MA'),
    (4, 'TX'),
    (5, NULL),
    (6, 'FL');
```

--------------------------------

### Insert Initial Data for Path Management

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Inserts a root node into the t32786 table. This node has no parent.

```sql
INSERT INTO t32786 VALUES ('3AAA2577-DBC3-47E7-9E85-9CC7E19CF48A', null, null)
```

--------------------------------

### Create Table pkBAC

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Creates a table named 'pkBAC' with columns a, b, c, d and a primary key on (b, a, c).

```sql
CREATE TABLE pkBAC (a INT, b INT, c INT, d INT, PRIMARY KEY(b,a,c))
```

--------------------------------

### Insert Data into Test Table

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Populates the test table with sample data for query execution.

```sql
INSERT INTO t_48638 values (1, 4);
```

```sql
INSERT INTO t_48638 values (4, 3);
```

```sql
INSERT INTO t_48638 values (3, 2);
```

```sql
INSERT INTO t_48638 values (4, 1);
```

```sql
INSERT INTO t_48638 values (1, 2);
```

```sql
INSERT INTO t_48638 values (6, 5);
```

```sql
INSERT INTO t_48638 values (7, 8);
```

--------------------------------

### Table Creation and Insertion for Joins

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Creates a table 's' with an integer column and inserts ten rows, likely for testing join performance or specific join types.

```sql
CREATE TABLE s(x INT)
```

```sql
INSERT INTO s(x) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
```

--------------------------------

### Inner Join with Table Creation and Insertion

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Sets up two tables, 'a' and 'b', with integer and boolean columns respectively, and inserts data. Then performs an inner join.

```sql
CREATE TABLE a (i int)
```

```sql
INSERT INTO a VALUES (1), (2), (3)
```

```sql
CREATE TABLE b (i int, b bool)
```

```sql
INSERT INTO b VALUES (2, true), (3, true), (4, false)
```

```sql
SELECT * FROM a INNER JOIN b ON a.i = b.i ORDER BY a.i, b.i, b.b
```

--------------------------------

### Create and Query Empty Table

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Creates an empty table named 'empty' with an integer column 'x' and then performs various join operations with 'onecolumn' to show results when one table is empty.

```sql
statement ok
CREATE TABLE `empty` (x INT)
```

--------------------------------

### Query with mariadb-java-client

Source: https://github.com/dolthub/go-mysql-server/blob/main/SUPPORTED_CLIENTS.md

Connect to a MariaDB database using the mariadb-java-client and execute a SQL query. Results are processed row by row. Ensure resources are closed using try-with-resources.

```java
package org.testing.mariadbjavaclient;

import java.sql.*;

class Main {
    public static void main(String[] args) {
        String dbUrl = "jdbc:mariadb://127.0.0.1:3306/mydb?user=root&password=";
        String query = "SELECT * FROM mytable LIMIT 1";

        try (Connection connection = DriverManager.getConnection(dbUrl)) {
            try (PreparedStatement stmt = connection.prepareStatement(query)) {
                try (ResultSet rs = stmt.executeQuery()) {
                    while (rs.next()) {
                        // use rs
                    }
                }
            }
        } catch (SQLException e) {
            // handle failure
        }
    }
}
```

--------------------------------

### Convert SQLLogicTest Files

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/README.md

Use this command to convert SQLLogicTest files from CockroachDB's PostgreSQL syntax format to the standard SQLLogicTest format. The converter does not handle syntax differences between PostgreSQL and MySQL.

```shell
go run ./convert/convert.go <infile> <outfile>
```

--------------------------------

### Join Using Specific Columns

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Demonstrates joins using the USING clause with one, two, or three common columns. Ensures correct results based on the specified join keys.

```sql
SELECT * FROM foo JOIN bar USING (b)
----
```

```sql
SELECT * FROM foo JOIN bar USING (a, b)
----
```

```sql
SELECT * FROM foo JOIN bar USING (a, b, c)
----
```

--------------------------------

### Create Users Table

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Creates a 'users' table with an integer ID and a VARCHAR name. This table is used to store user information for testing joins and subqueries.

```sql
CREATE TABLE users (
    id INT8 NOT NULL,
    name VARCHAR(50),
    PRIMARY KEY (id)
);
```

--------------------------------

### Table Creation for Referential Integrity

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Creates 'customers' and 'orders' tables to demonstrate referential integrity constraints, often used in join scenarios.

```sql
CREATE TABLE customers(id INT PRIMARY KEY NOT NULL)
```

```sql
CREATE TABLE orders(id INT, cust INT REFERENCES customers(id))
```

--------------------------------

### Connect and Query with PHP PDO

Source: https://github.com/dolthub/go-mysql-server/blob/main/SUPPORTED_CLIENTS.md

Use PHP's PDO to connect to the database and execute a query. Results are fetched as an associative array. Errors are caught and can be handled.

```php
try {
    $conn = new PDO("mysql:host=127.0.0.1:3306;dbname=mydb", "root", "");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $stmt = $conn->query('SELECT * FROM mytable LIMIT 1');
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // use result
} catch (PDOException $e) {
    // handle error
}
```

--------------------------------

### Select Customers with Orders Shipped to WY using IN

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Finds customers who have at least one order shipped to 'WY' using an IN subquery.

```sql
SELECT * FROM c WHERE 'WY' IN (SELECT ship FROM o WHERE o.c_id=c.c_id);
```

--------------------------------

### Customers with billing state matching any non-NULL shipping state or is NULL

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Finds customers whose billing state matches any non-NULL shipping state or whose billing state itself is NULL.

```sql
SELECT c_id, bill = ANY(SELECT ship FROM o WHERE ship IS NOT NULL) OR bill IS NULL FROM c;
```

--------------------------------

### Join with Subquery as Data Source

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Demonstrates joining a table with a subquery that generates data, using the USING clause for the join condition.

```sql
SELECT * FROM onecolumn JOIN (SELECT column_0 as x FROM (VALUES ROW(41), ROW(42), ROW(43)) a) AS a USING(x)
```

--------------------------------

### Star Expansion from Anonymous Sources

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Demonstrates star expansion (*) from two anonymous subqueries, specifying an order for the results.

```sql
SELECT * FROM (SELECT * FROM onecolumn) sq1, (SELECT * FROM onecolumn) sq2 ORDER BY sq1.x, sq2.x
```

--------------------------------

### Ambiguity Resolution with Anonymous Sources

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Shows how to join anonymous sources correctly when they are properly qualified, preventing ambiguity errors.

```sql
SELECT x FROM (onecolumn JOIN othercolumn USING (x)) JOIN (onecolumn AS a JOIN othercolumn AS b USING(x)) USING(x)
```

--------------------------------

### Select Customers with Orders Shipped to WY or WA using IN

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Finds customers with at least one order shipped to 'WY' or 'WA' using multiple IN subqueries with OR.

```sql
SELECT *
  FROM c
  WHERE
    'WY' IN (SELECT ship FROM o WHERE o.c_id=c.c_id)
    OR 'WA' IN (SELECT ship FROM o WHERE o.c_id=c.c_id);
```

--------------------------------

### Insert Stuff Data

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Inserts sample data into the 'stuff' table, associating items with specific users and dates. This data is used to test joins and correlated subqueries.

```sql
INSERT INTO stuff(id, date, user_id) VALUES (1, '2007-10-15', 1);
```

```sql
INSERT INTO stuff(id, date, user_id) VALUES (2, '2007-12-15', 1);
```

```sql
INSERT INTO stuff(id, date, user_id) VALUES (3, '2007-11-15', 1);
```

```sql
INSERT INTO stuff(id, date, user_id) VALUES (4, '2008-01-15', 2);
```

```sql
INSERT INTO stuff(id, date, user_id) VALUES (5, '2007-06-15', 3);
```

```sql
INSERT INTO stuff(id, date, user_id) VALUES (6, '2007-03-15', 3);
```

--------------------------------

### Create Table for Testing

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Defines a table with a composite primary key for testing join operations.

```sql
CREATE TABLE IF NOT EXISTS t_48638 (
  `key` INT NOT NULL,
  `value` INTEGER NOT NULL,
  PRIMARY KEY (`key`, `value`))
```

--------------------------------

### Insert Order Data

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Populates the 'o' table with sample order data, including NULL shipping states and orders linked to customers.

```sql
INSERT INTO o VALUES
    (10, 1, 'CA'), (20, 1, 'CA'), (30, 1, 'CA'),
    (40, 2, 'CA'), (50, 2, 'TX'), (60, 2, NULL),
    (70, 4, 'WY'), (80, 4, NULL),
    (90, 6, 'WA');
```

--------------------------------

### Multiple Table Aliases for Single Column

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Illustrates joining a table with itself multiple times, each with a distinct alias, using the USING clause on a common column.

```sql
SELECT * FROM (twocolumn AS a JOIN twocolumn AS b USING(x) JOIN twocolumn AS c USING(x)) ORDER BY x LIMIT 1
```

--------------------------------

### Select All Customers using EXISTS and NOT EXISTS

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Retrieves all customers by combining EXISTS and NOT EXISTS conditions with an OR operator.

```sql
SELECT *
  FROM c
  WHERE
    EXISTS(SELECT * FROM o WHERE o.c_id=c.c_id)
    OR NOT EXISTS(SELECT * FROM o WHERE o.c_id=c.c_id);
```

--------------------------------

### Create Pairs Table

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Creates a table named 'pairs' with two integer columns, 'a' and 'b'. Used for join tests.

```sql
CREATE TABLE pairs (a INT, b INT)
```

--------------------------------

### SQL Syntax for Custom Index Driver

Source: https://github.com/dolthub/go-mysql-server/blob/main/BACKEND.md

Use the `USING driverid` extension syntax in your `CREATE INDEX` statement to specify a custom index driver.

```sql
CREATE INDEX foo ON table USING driverid (col1, col2)
```

--------------------------------

### Select Customers with No Matching Bill and Ship Addresses using NOT IN (with NULL)

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Finds customers whose billing state does not match the shipping state of any of their orders, including cases with NULL shipping states.

```sql
SELECT * FROM c WHERE bill NOT IN (SELECT ship FROM o WHERE o.c_id=c.c_id);
```

--------------------------------

### Customers with all orders matching billing address AND at least one order

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Identifies customers whose billing address matches all their shipping addresses and who have at least one order.

```sql
SELECT
    c_id,
    bill = ALL(SELECT ship FROM o WHERE o.c_id=c.c_id)
    AND EXISTS(SELECT * FROM o WHERE o.c_id=c.c_id)
  FROM c
ORDER BY c_id;
```

--------------------------------

### Customers where billing state < any shipping state results in NOT NULL

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Identifies customers for whom the comparison 'billing state < any shipping state' yields a non-NULL result.

```sql
SELECT c_id, (bill < ANY(SELECT ship FROM o WHERE o.c_id=c.c_id)) IS NOT NULL FROM c ORDER BY c_id;
```

--------------------------------

### LEFT OUTER JOIN with Empty Table using USING

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Performs a LEFT OUTER JOIN from 'onecolumn' to an empty table using the USING clause. All rows from 'onecolumn' are preserved, with NULLs for the joined column from the empty table.

```sql
query I nosort
SELECT * FROM onecolumn AS a LEFT OUTER JOIN `empty` AS b USING(x) ORDER BY x
----
```

--------------------------------

### Select Customers with No Matching Bill and Ship Addresses using NOT IN (excluding NULL)

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Finds customers whose billing state does not match the shipping state of any of their non-NULL shipping state orders.

```sql
SELECT * FROM c WHERE bill NOT IN (SELECT ship FROM o WHERE o.c_id=c.c_id AND ship IS NOT NULL);
```

--------------------------------

### Select Customers with No Matching Bill and Ship Addresses using NOT IN (only NULL)

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Finds customers whose billing state does not match any of their orders that have a NULL shipping state.

```sql
SELECT * FROM c WHERE bill NOT IN (SELECT ship FROM o WHERE o.c_id=c.c_id AND ship IS NULL);
```

--------------------------------

### Customers with all orders matching billing address OR at least one order shipped to WY

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Selects customers where their billing address matches all their shipping addresses, or if any order was shipped to 'WY'.

```sql
SELECT
    c_id,
    bill = ALL(SELECT ship FROM o WHERE o.c_id=c.c_id)
    OR EXISTS(SELECT * FROM o WHERE o.c_id=c.c_id AND ship='WY')
  FROM c
ORDER BY c_id;
```

--------------------------------

### Create Table t1

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Creates a table named 't1' with four integer columns: col1, x, col2, and y.

```sql
CREATE TABLE t1 (col1 INT, x INT, col2 INT, y INT)
```

--------------------------------

### Customers where billing state > any shipping state results in NOT NULL

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Selects customers for whom the comparison 'billing state > any shipping state' evaluates to a non-NULL value.

```sql
SELECT c_id, (bill > ANY(SELECT ship FROM o WHERE o.c_id=c.c_id)) IS NOT NULL FROM c ORDER BY c_id;
```

--------------------------------

### Create Table t2

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Creates a table named 't2' with four integer columns: col3, y, x, and col4.

```sql
CREATE TABLE t2 (col3 INT, y INT, x INT, col4 INT)
```

--------------------------------

### NATURAL JOIN with Multiple Columns

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Performs a NATURAL JOIN between 'onecolumn' and 'onecolumn_w', joining on columns with common names ('x' and 'w' are not common, so it joins on 'x' from 'onecolumn' and 'w' from 'onecolumn_w' if they were named the same, but here it joins on 'x' from 'onecolumn' and 'w' from 'onecolumn_w' as they are the only columns). The result is ordered by 'x' and 'w'.

```sql
query II nosort
SELECT * FROM onecolumn AS a NATURAL JOIN onecolumn_w as b ORDER BY x, w
----
```

--------------------------------

### CREATE TABLE for Regression Test

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Defines two simple tables 'l' and 'r' with integer primary keys and one integer column each. Used for regression testing specific join scenarios.

```sql
CREATE TABLE l (a INT PRIMARY KEY, b1 INT)
```

```sql
CREATE TABLE r (a INT PRIMARY KEY, b2 INT)
```

```sql
INSERT INTO l VALUES (1, 1), (2, 1), (3, 1)
```

```sql
INSERT INTO r VALUES (2, 1), (3, 1), (4, 1)
```

--------------------------------

### Create Table for Path Management

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Creates a table to store hierarchical data with an ID, parent ID, and a calculated parent path. This is used for testing recursive path updates.

```sql
CREATE TABLE t32786 (id VARCHAR(36) PRIMARY KEY, parent_id VARCHAR(36), parent_path text)
```

--------------------------------

### Select Customers with Bill State Less Than Any Ship State using ANY

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Retrieves customers whose billing state is lexicographically less than the shipping state of at least one of their orders.

```sql
SELECT * FROM c WHERE bill < ANY(SELECT ship FROM o WHERE o.c_id=c.c_id);
```

--------------------------------

### INNER JOIN with USING Clause

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Executes an INNER JOIN using the 'USING' clause on the 'x' column. This is a shorthand for joining on columns with the same name.

```sql
query I nosort
SELECT * FROM onecolumn AS a JOIN onecolumn as b USING(x) ORDER BY x
----
```

--------------------------------

### Select Customers where Bill State < Any Ship State is NOT NULL

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Identifies customers for whom the condition 'bill < ANY(ship)' evaluates to NOT NULL, indicating a successful comparison.

```sql
SELECT * FROM c WHERE (bill < ANY(SELECT ship FROM o WHERE o.c_id=c.c_id)) IS NOT NULL;
```

--------------------------------

### Query with .NET Core MySqlConnector

Source: https://github.com/dolthub/go-mysql-server/blob/main/SUPPORTED_CLIENTS.md

Use the MySqlConnector library in C# to asynchronously open a connection, execute a query, and read the results. Ensure the connection and command are disposed.

```csharp
using MySql.Data.MySqlClient;
using System.Threading.Tasks;

namespace something
{
    public class Something
    {
        public async Task DoQuery()
        {
            var connectionString = "server=127.0.0.1;user id=root;password=;port=3306;database=mydb;";

            using (var conn = new MySqlConnection(connectionString))
            {
                await conn.OpenAsync();

                var sql = "SELECT * FROM mytable LIMIT 1";

                using (var cmd = new MySqlCommand(sql, conn))
                using (var reader = await cmd.ExecuteReaderAsync()) {
                    while (await reader.ReadAsync()) {
                        // use reader
                    }
                }
            }
        }
    }
}
```

--------------------------------

### INNER JOIN with Empty Table using USING

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Performs an INNER JOIN between 'onecolumn' and an empty table using the USING clause. The result is empty because the 'empty' table has no matching columns or rows.

```sql
query I nosort
SELECT * FROM onecolumn AS a JOIN `empty` AS b USING(x)
----
```

--------------------------------

### Insert Users Data

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Inserts sample data into the 'users' table. This data will be used in subsequent queries to test relationships and filtering.

```sql
INSERT INTO users(id, name) VALUES (1, 'user1');
```

```sql
INSERT INTO users(id, name) VALUES (2, 'user2');
```

```sql
INSERT INTO users(id, name) VALUES (3, 'user3');
```

--------------------------------

### Create Square Table

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Creates a table named 'square' with columns 'n' (primary key) and 'sq'. Used for join tests.

```sql
CREATE TABLE square (n INT PRIMARY KEY, sq INT)
```

--------------------------------

### Select customers with all orders matching bill state or any order shipped to WY

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Selects customers where all their orders have a 'bill' state equal to the 'ship' state, OR at least one order was shipped to 'WY'. This demonstrates the use of ALL and EXISTS operators.

```sql
SELECT *
  FROM c
  WHERE
    bill = ALL(SELECT ship FROM o WHERE o.c_id=c.c_id)
    OR EXISTS(SELECT * FROM o WHERE o.c_id=c.c_id AND ship='WY');
```

--------------------------------

### Insert Data into Pairs Table

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Inserts sample data into the 'pairs' table. This data is used in subsequent join queries.

```sql
INSERT INTO pairs VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (1,6), (2,3), (2,4), (2,5), (2,6), (3,4), (3,5), (3,6), (4,5), (4,6)
```

--------------------------------

### LEFT OUTER JOIN with Empty Table

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Demonstrates a LEFT OUTER JOIN from 'onecolumn' to an empty table. All rows from 'onecolumn' are returned, with NULLs for columns from the empty table.

```sql
query II nosort
SELECT a.x AS x, b.x AS y FROM onecolumn AS a LEFT OUTER JOIN `empty` AS b ON a.x = b.x ORDER BY a.x
----
```

--------------------------------

### Select customers where bill state matches any ship state or is NULL

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Selects customers where the 'bill' state matches any 'ship' state or the 'bill' state is NULL. This combines the ANY operator with a NULL check.

```sql
SELECT * FROM c WHERE bill = ANY(SELECT ship FROM o) OR bill IS NULL;
```

--------------------------------

### Customers where billing state < any shipping state results in NULL

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Checks for customers where the comparison 'billing state < any shipping state' evaluates to NULL, indicating indeterminate results due to NULLs.

```sql
SELECT c_id, (bill < ANY(SELECT ship FROM o WHERE o.c_id=c.c_id)) IS NULL FROM c ORDER BY c_id;
```

--------------------------------

### Customers where billing state > any shipping state results in NULL

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Tests for customers where the comparison 'billing state > any shipping state' results in NULL, typically due to NULL values in the data.

```sql
SELECT c_id, (bill > ANY(SELECT ship FROM o WHERE o.c_id=c.c_id)) IS NULL FROM c ORDER BY c_id;
```

--------------------------------

### Customers with no order matching billing address (NULL ship)

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Selects customer IDs where the billing address is not found in any order's shipping address, specifically when the shipping address is NULL.

```sql
SELECT c_id, bill NOT IN (SELECT ship FROM o WHERE o.c_id=c.c_id AND ship IS NULL)
  FROM c
ORDER BY c_id;
```

--------------------------------

### Select specific columns from JOIN USING(x)

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Performs an inner join between 't1' and 't2' using the 'x' column and selects specific columns, including 'x' from both tables and the aliased 'x' from t2.

```sql
SELECT t2.x, t1.x, x FROM t1 JOIN t2 USING(x)
```

--------------------------------

### Apply: Subquery in WHERE clause with IN

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Applies a filter where the minimum shipping address for a customer must be present in the list of shipping addresses for that same customer.

```sql
SELECT *
  FROM c
  WHERE (SELECT min(ship) FROM o WHERE o.c_id=c.c_id) IN (SELECT ship FROM o WHERE o.c_id=c.c_id);
```

--------------------------------

### Full Outer Join with Duplicate Matches

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Demonstrates a right outer join after inserting a duplicate value into table 'b'. The query shows how duplicate matches are handled.

```sql
INSERT INTO b VALUES (3, false)
```

```sql
SELECT * FROM a RIGHT OUTER JOIN b ON a.i=b.i ORDER BY b.i, b.b
```

--------------------------------

### CROSS JOIN with NULL Values

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Demonstrates a CROSS JOIN between 'onecolumn' and itself, showing how NULL values are handled in the result set. The output is ordered by the joined columns.

```sql
query II nosort
SELECT a.x AS x, b.x AS y FROM onecolumn AS a CROSS JOIN onecolumn AS b ORDER BY x, y
----
```

--------------------------------

### LEFT OUTER JOIN with ON clause and specific conditions

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Performs a LEFT OUTER JOIN using an ON clause with multiple conditions, including equality on key columns and range checks. Demonstrates handling of NULLs for non-matches.

```sql
SELECT * FROM xyu LEFT OUTER JOIN xyv ON xyu.x = xyv.x AND xyu.y = xyv.y AND xyu.x = 1 AND xyu.y < 10
----
```

--------------------------------

### Select customers where bill state > any ship state is NULL

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Tests the behavior of the ANY operator when the comparison results in NULL. This is used to ensure that ANY is not normalized into EXISTS.

```sql
SELECT * FROM c WHERE (bill > ANY(SELECT ship FROM o WHERE o.c_id=c.c_id)) IS NULL;
```

--------------------------------

### Read Table with SQLAlchemy and Pandas

Source: https://github.com/dolthub/go-mysql-server/blob/main/SUPPORTED_CLIENTS.md

Utilize SQLAlchemy to create an engine and Pandas to read a database table into a DataFrame. Iterate over the DataFrame's columns.

```python
import pandas as pd
import sqlalchemy

engine = sqlalchemy.create_engine('mysql+pymysql://root:@127.0.0.1:3306/mydb')
with engine.connect() as conn:
     repo_df = pd.read_sql_table("mytable", con=conn)
     for table_name in repo_df.to_dict():
        print(table_name)
```

--------------------------------

### CREATE TABLE with Composite Primary Key

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/join.txt

Defines tables with composite primary keys consisting of multiple integer columns. Used for testing joins on multi-column keys.

```sql
CREATE TABLE xyu (x INT, y INT, u INT, PRIMARY KEY(x,y,u))
```

```sql
INSERT INTO xyu VALUES (0, 0, 0), (1, 1, 1), (3, 1, 31), (3, 2, 32), (4, 4, 44)
```

```sql
CREATE TABLE xyv (x INT, y INT, v INT, PRIMARY KEY(x,y,v))
```

```sql
INSERT INTO xyv VALUES (1, 1, 1), (2, 2, 2), (3, 1, 31), (3, 3, 33), (5, 5, 55)
```

--------------------------------

### Select customers where bill state matches any ship state

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Selects customers where the 'bill' state matches any 'ship' state from their orders. This demonstrates the use of the = ANY operator.

```sql
SELECT * FROM c WHERE bill = ANY(SELECT ship FROM o);
```

--------------------------------

### Customers with billing state matching any non-NULL shipping state

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Identifies customers whose billing state matches at least one non-NULL shipping state from their orders.

```sql
SELECT c_id, bill = ANY(SELECT ship FROM o WHERE ship IS NOT NULL) FROM c;
```

--------------------------------

### Select Customers with No Orders using NOT EXISTS

Source: https://github.com/dolthub/go-mysql-server/blob/main/enginetest/sqllogictest/testdata/join/subquery_correlated.txt

Retrieves customers who have no associated orders using a NOT EXISTS subquery.

```sql
SELECT * FROM c WHERE NOT EXISTS(SELECT * FROM o WHERE o.c_id=c.c_id);
```