SQL Test Queries AND OR

This page is my SQL query reference sheet. All these queries are tested with every build so that the outcoming xml shall remain correct.

I imagine this list to grow quite a bit over time, and I think it's important that it exists.

These tests can be found in camlsql.sql_and-or_testqueries.spec.js and here I will attempt to cover different AND/OR statements with and without groups.

SQL AND/OR Test Query #001

1. SELECT * FROM List WHERE Field1 = ? AND Field2 = ?, ['Value 1', 'Value 2']
2. SELECT * FROM List WHERE ( Field1 = ? AND Field2 = ? ), ['Value 1', 'Value 2']
3. SELECT * FROM List WHERE ( Field1 = ? AND Field2 = ? ), ['Value 1', 'Value 2']

<View>
    <Query>
        <Where>
            <And>
                <Eq>
                    <FieldRef Name="Field1" />
                    <Value Type="Text">Value 1</Value>
                </Eq>
                <Eq>
                    <FieldRef Name="Field2" />
                    <Value Type="Text">Value 2</Value>
                </Eq>
            </And>
        </Where>
    </Query>
</View>

SQL AND/OR Test Query #002

1. SELECT * FROM List WHERE (Field1 = ? AND Field2 = ?) OR Field2 = ?, ['Value 1', 'Value 2', 'Value 3']
2. SELECT * FROM List WHERE (Field1 = ? AND Field2 = ?) OR (Field2 = ?), ['Value 1', 'Value 2', 'Value 3']
3. SELECT * FROM List WHERE ((Field1 = ? AND Field2 = ?)) OR (Field2 = ?), ['Value 1', 'Value 2', 'Value 3']
4. SELECT * FROM List WHERE ((Field1 = ? AND Field2 = ?)) OR ((Field2 = ?) ), ['Value 1', 'Value 2', 'Value 3']

<View>
    <Query>
        <Where>
            <Or>
                <And>
                    <Eq>
                        <FieldRef Name="Field1" />
                        <Value Type="Text">Value 1</Value>
                    </Eq>
                    <Eq>
                        <FieldRef Name="Field2" />
                        <Value Type="Text">Value 2</Value>
                    </Eq>
                </And>
                <Eq>
                    <FieldRef Name="Field2" />
                    <Value Type="Text">Value 3</Value>
                </Eq>
            </Or>
        </Where>
    </Query>
</View>

SQL AND/OR Test Query #003

1. "SELECT * FROM List WHERE Field1 = ? AND Field2 = ? OR (Field2 = ?)", ['Value 1', 'Value 2', 'Value 3']
2. "SELECT * FROM List WHERE (Field1 = ? AND Field2 = ?) OR (Field2 = ?)", ['Value 1', 'Value 2', 'Value 3']

<View>
    <Query>
        <Where>
            <Or>
                <And>
                    <Eq>
                        <FieldRef Name="Field1" />
                        <Value Type="Text">Value 1</Value>
                    </Eq>
                    <Eq>
                        <FieldRef Name="Field2" />
                        <Value Type="Text">Value 2</Value>
                    </Eq>
                </And>
                <Eq>
                    <FieldRef Name="Field2" />
                    <Value Type="Text">Value 3</Value>
                </Eq>
            </Or>
        </Where>
    </Query>
</View>

SQL AND/OR Test Query #004

1. "SELECT * FROM List WHERE Field1 = ? AND Field2 = ? OR Field2 = ?", ['Value 1', 'Value 2', 'Value 3']
2. "SELECT * FROM List WHERE Field1 = ? AND (Field2 = ? OR Field2 = ?)", ['Value 1', 'Value 2', 'Value 3']

<View>
    <Query>
        <Where>
            <And>
                <Eq>
                    <FieldRef Name="Field1" />
                    <Value Type="Text">Value 1</Value>
                </Eq>
                <Or>
                    <Eq>
                        <FieldRef Name="Field2" />
                        <Value Type="Text">Value 2</Value>
                    </Eq>
                    <Eq>
                        <FieldRef Name="Field2" />
                        <Value Type="Text">Value 3</Value>
                    </Eq>
                </Or>
            </And>
        </Where>
    </Query>
</View>

SQL AND/OR Test Query #005

1. "SELECT * FROM List WHERE (Field1 = ? AND Field2 = ?) AND Field3 = ? AND (Field4 = ? OR Field5 = ?)", [1, 2, 3, 4, 5]
2. "SELECT * FROM List WHERE (Field1 = ? AND Field2 = ?) AND (Field3 = ?) AND (Field4 = ? OR Field5 = ?)", [1, 2, 3, 4, 5]

<View>
    <Query>
        <Where>
            <And>
                <And>
                    <Eq>
                        <FieldRef Name="Field1" />
                        <Value Type="Number">1</Value>
                    </Eq>
                    <Eq>
                        <FieldRef Name="Field2" />
                        <Value Type="Number">2</Value>
                    </Eq>
                </And>
                <And>
                    <Eq>
                        <FieldRef Name="Field3" />
                        <Value Type="Number">3</Value>
                    </Eq>
                    <Or>
                        <Eq>
                            <FieldRef Name="Field4" />
                            <Value Type="Number">4</Value>
                        </Eq>
                        <Eq>
                            <FieldRef Name="Field5" />
                            <Value Type="Number">5</Value>
                        </Eq>
                    </Or>
                </And>
            </And>
        </Where>
    </Query>
</View>

SQL AND/OR Test Query #006

As a user I want to search for "hi world" and expect to find all pages where the body contains both terms so that I can narrow down my search by adding terms

1. "SELECT * FROM List WHERE (BodyText LIKE @term1 AND BodyText LIKE @term2) OR (BodyText LIKE @term1 AND BodyText LIKE @term2)", {"@term1" : "hi", "@term2" : "world"}
2. "SELECT * FROM List WHERE ((BodyText LIKE @term1 AND BodyText LIKE @term2) OR (BodyText LIKE @term1 AND BodyText LIKE @term2))", {"@term1" : "hi", "@term2" : "world"}

<View>
    <Query>
        <Where>
            <Or>
                <And>
                    <Contains>
                        <FieldRef Name="BodyText" />
                        <Value Type="Text">hi</Value>
                    </Contains>
                    <Contains>
                        <FieldRef Name="BodyText" />
                        <Value Type="Text">world</Value>
                    </Contains>
                </And>
                <And>
                    <Contains>
                        <FieldRef Name="BodyText" />
                        <Value Type="Text">hi</Value>
                    </Contains>
                    <Contains>
                        <FieldRef Name="BodyText" />
                        <Value Type="Text">world</Value>
                    </Contains>
                </And>
            </Or>
        </Where>
    </Query>
</View>