Advanced SQL

UNION

The union sentence is used to accumulate results for two SELECT sentences.

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2

We have the following tables

company1

company2

For the following query

SELECT name, surname FROM company1
UNION
SELECT name, surname FROM company2

We’d get the following results

Note that the person ANTONIO PEREZ only appears once, even though it’s a duplicated entry.

UNION ALL

It’s used to get results for both queries and we will also retrieve duplicated entries. For the above example and the following query

SELECT name, surname FROM company1
UNION ALL
SELECT name, surname FROM company2

we’d get

Notice ANTONIO PEREZ is now duplicated.

ON UPDATE CASCADE

We have the following declaration

CREATE TABLE parent (
	id INT NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (id)
);

CREATE TABLE child_delete (
	id INT NOT NULL AUTO_INCREMENT, 
	parent_id INT,
	INDEX par_ind (parent_id),
	FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
);

CREATE TABLE child_update (
	id INT NOT NULL AUTO_INCREMENT, 
	parent_id INT,
	INDEX par_ind (parent_id),
	FOREIGN KEY (parent_id) REFERENCES parent(id) ON UPDATE CASCADE
);

For ON DELETE CASCADE if a parent with an id is deleted, a child record with a reference to that parent idwill also be automatically deleted.

With ON UPDATE CASCADE this means if the parent id is changed, the same id for the child row will also be automatically changed. This isn’t useful if your PK is just an identity value with auto-increment, but it’s more useful when your PK is something like (for example) a JIRA id which references a project VAR-7865 which may be subject to changes if the issue changes from one project to another.

Reference(s)

http://sql.11sql.com/sql-union.htm https://es.stackoverflow.com/questions/37880/cu%C3%A1l-es-la-diferencia-entre-union-y-union-all https://stackoverflow.com/questions/1481476/when-to-use-on-update-cascade https://es.stackoverflow.com/questions/37880/cu%C3%A1l-es-la-diferencia-entre-union-y-union-all https://stackoverflow.com/questions/1481476/when-to-use-on-update-cascade