L'operador UNION s'utilitza per combinar el conjunt de resultats de dues o més sentències SELECT. - Cada instrucció SELECT dins d'UNION ha de tenir el mateix nombre de columnes - Les columnes també han de tenir tipus de dades similars - Les columnes de cada instrucció SELECT també han d'estar en el mateix ordre
Exemple SQL UNION
La següent instrucció SQL retorna les ciutats (només valors diferents) tant de la taula "Clients" com de la taula "Proveïdors":
SQL:
SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;
GenQL:
Set<String> cities = new TreeSet<String>();
cities.addAll(getCustomerManager().executeQuery(CustomersFields.CITY));
cities.addAll(getSuppliersManager().executeQuery(SuppliersFields.CITY));
print(new ArrayList<String>(cities), SuppliersFields.CITY.javaName);
SQL UNION ALL Exemple
La següent instrucció SQL retorna les ciutats (també els valors duplicats) tant de la taula "Clients" com de la taula "Proveïdors":
SQL:
SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;
GenQL:
List<String> cities = new ArrayList<String>();
cities.addAll(getCustomerManager().executeQuery(CustomersFields.CITY));
cities.addAll(getSuppliersManager().executeQuery(SuppliersFields.CITY));
Collections.sort(cities);
print(cities, SuppliersFields.CITY.javaName);
SQL UNION Amb ON
La següent instrucció SQL retorna les ciutats alemanyes (només valors diferents) tant de la taula "Clients" com de la taula "Proveïdors":
SQL:
SELECT City, Country FROM Customers WHERE Country='Germany' UNION SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City;
GenQL:
Comparator<Select2Values<String, String>> c = new Comparator<Select2Values<String, String>>() {
@Override
public int compare(Select2Values<String, String> o1, Select2Values<String, String> o2) {
if (o1 == null) {
if (o2 == null) {
return 0;
} else {
return "null".compareToIgnoreCase(o2.getValue1());
}
} else {
if (o2 == null) {
return o1.getValue1().compareToIgnoreCase("null");
} else {
return o1.getValue1().compareToIgnoreCase(o2.getValue1());
}
}
}
};
Set<Select2Values<String, String>> cityCountrySet = new TreeSet<Select2Values<String, String>>(c);
{
Select2Columns<String, String> s2c;
s2c = new Select2Columns<String, String>(CustomersFields.CITY.select, CustomersFields.COUNTRY.select);
cityCountrySet.addAll(getCustomerManager().executeQuery(s2c, CustomersFields.COUNTRY.equal("Germany"), new OrderBy(CustomersFields.CITY)));
}
{
Select2Columns<String, String> s2c;
s2c = new Select2Columns<String, String>(SuppliersFields.CITY.select, SuppliersFields.COUNTRY.select);
cityCountrySet.addAll(getSuppliersManager().executeQuery(s2c, SuppliersFields.COUNTRY.equal("Germany"), new OrderBy(SuppliersFields.CITY)));
}
print(new ArrayList<Select2Values<String, String>>(cityCountrySet), CustomersFields.CITY.javaName, CustomersFields.COUNTRY.javaName);
SQL UNION ALL Amb ON
La següent instrucció SQL retorna les ciutats alemanyes (també els valors duplicats) tant de la taula "Clients" com de la taula "Proveïdors".
SQL:
SELECT City, Country FROM Customers WHERE Country='Germany' UNION ALL SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City;
GenQL:
List<Select2Values<String, String>> cityCountryList = new ArrayList<Select2Values<String, String>>();
{
Select2Columns<String, String> s2c;
s2c = new Select2Columns<String, String>(CustomersFields.CITY.select, CustomersFields.COUNTRY.select);
Where w = CustomersFields.COUNTRY.equal("Germany");
OrderBy o = new OrderBy(CustomersFields.CITY);
cityCountryList.addAll(getCustomerManager().executeQuery(s2c, w, o));
}
{
Select2Columns<String, String> s2c;
s2c = new Select2Columns<String, String>(SuppliersFields.CITY.select, SuppliersFields.COUNTRY.select);
Where w = SuppliersFields.COUNTRY.equal("Germany");
OrderBy o = new OrderBy(SuppliersFields.CITY);
cityCountryList.addAll(getSuppliersManager().executeQuery(s2c, w, o));
}
Comparator<Select2Values<String, String>> c = new Comparator<Select2Values<String, String>>() {
@Override
public int compare(Select2Values<String, String> o1, Select2Values<String, String> o2) {
if (o1 == null) {
if (o2 == null) {
return 0;
} else {
return "null".compareToIgnoreCase(o2.getValue1());
}
} else {
if (o2 == null) {
return o1.getValue1().compareToIgnoreCase("null");
} else {
return o1.getValue1().compareToIgnoreCase(o2.getValue1());
}
}
}
};
Collections.sort(cityCountryList, c);
print(new ArrayList<Select2Values<String, String>>(cityCountryList), CustomersFields.CITY.javaName, CustomersFields.COUNTRY.javaName);
Un altre exemple UNI�?
La següent instrucció SQL enumera tots els clients i proveïdors:
SQL:
SELECT 'Customer' AS Type, ContactName, City, Country
FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers;
GenQL: