How to emulate tagged union in a database?
Some people use a design called Polymorphic Associations to do this, allowing vehicle_id to contain a value that exists either in car or motor tables. Then add a vehicle_type that names the table which the given row in t1 references.
The trouble is that you can't declare a real SQL foreign key constraint if you do this. There's no support in SQL for a foreign key that has multiple reference targets. There are other problems, too, but the lack of referential integrity is already a deal-breaker.
A better design is to borrow a concept from OO design of a common supertype of both car and motor:
CREATE TABLE Identifiable (
id SERIAL PRIMARY KEY
);
Then make t1 reference this super-type table:
CREATE TABLE t1 (
vehicle_id INTEGER NOT NULL,
FOREIGN KEY (vehicle_id) REFERENCES identifiable(id)
...
);
And also make the sub-types reference their parent supertype. Note that the primary key of the sub-types is not auto-incrementing. The parent supertype takes care of allocating a new id value, and the children only reference that value.
CREATE TABLE car (
id INTEGER NOT NULL,
FOREIGN KEY (id) REFERENCES identifiable(id)
...
);
CREATE TABLE motor (
id INTEGER NOT NULL,
FOREIGN KEY (id) REFERENCES identifiable(id)
...
);
Now you can have true referential integrity, but also support multiple subtype tables with their own attributes.
The answer by @Quassnoi also shows a method to enforce disjoint subtypes. That is, you want to prevent both car and motor from referencing the same row in their parent supertype table. When I do this, I use a single-column primary key for Identifiable.id but also declare a UNIQUE key over Identifiable.(id, type). The foreign keys in car and motor can reference the two-column unique key instead of the primary key.
SQL - JOIN using UNION ?? UNION using JOIN?
An interview is the framework on which you set out your wares. Remember: don't answer questions ;)
Think of a press conference: the spokesperson is not looking to answer difficult questions from journos to catch themselves out. Rather, they are looking for questions to which they already have answers, being the information they want to release (and no more!)
If I faced this question in an interview, I would use it to demonstrate my knowledge of relational algebra because that's what I'd have gone into the interview with the intention of doing; I be alert for the "Talk about relational algebra here" question and this would be it.
Loosely speaking, JOIN is the counterpart of logical AND, whereas UNION is the counterpart of logical OR. Therefore, similar questions using convention logic could be, "Can you do AND using OR?" and "Can you do OR using AND?" The answer would depend on what else you could use e.g. NOT might come in handy ;)
I'd also be tempted to discuss the differences between the set of primitive operators, the set of operators necessary for computational completeness and the set of operators and shorthands required for practical purposes.
Trying to answer the question directly raises further questions. JOIN implies 'natural join' in relational algebra whereas in SQL it implies INNER JOIN. If the question specifically relates to SQL, do you have to answer for all the JOIN types? What about UNION JOIN?
To employ one example, SQL's outer join is famously a UNION. Chris Date expresses it better than I could ever hope to:
Outer join is expressly designed to
produce nulls in its result and should
therefore be avoided, in general.
Relationally speaking, it's a kind of
shotgun marriage: It forces tables
into a kind of union—yes, I do mean
union, not join—even when the tables
in question fail to conform to the
usual requirements for union (see
Chapter 6). It does this, in effect,
by padding one or both of the tables
with nulls before doing the union,
thereby making them conform to those
usual requirements after all. But
there's no reason why that padding
shouldn't be done with proper values
instead of nulls
SQL and Relational Theory, 1st Edition by C.J. Date
This would be a good discussion point if, "I hate nulls" is something you wanted to get across in the interview!
These are just a few thoughts that spring to mind. The crucial point is, by asking these questions the interviewer is offering you a branch. What will YOU hang on it? ;)
Efficient way to simulate full outer join in MySQL?
You can use a LEFT JOIN and a RIGHT JOIN:
SELECT * FROM tableA LEFT JOIN tableB ON tableA.b_id = tableB.id
UNION ALL
SELECT * FROM tableA RIGHT JOIN tableB ON tableA.b_id = tableB.id
WHERE tableA.b_id IS NULL
There is also some information on Wikipedia about this topic: Full outer join.
The Wikipedia article suggests using a UNION in MySQL. This is slightly slower than UNION ALL, but more importantly it won't always give the correct result - it will remove duplicated rows from the output. So prefer to use UNION ALL instead of UNION here.
Is it possible to create a discriminated union via a unit of measure tag in F#?
As I said in the comment, the simple answer is no.
In a way, you are trying to misuse one F# feature (units of measure) to emulate a feature that might exist in other languages (suffix operators), which is probably a bad thing to do in the first place, because (even if it was possible), the resulting code would be quite confusing.
If you simply want to reverse the order of the arguments so that the number comes before the unit name, you can use the piping operator and write:
let var1 = 10 |> Pixel
let var2 = Unset
This essentially gives you a way to write "suffix operators", but using standard F# idioms.
How to emulate a dependent CTE
There's no particular problem with doing that, except that you have to repeat tbl2:
SELECT *
FROM (
SELECT *
FROM (
SELECT 1 AS a
) tbl1
JOIN (
SELECT 1 AS a UNION ALL SELECT 2
) tbl2
USING (a)
) tbl3
CROSS JOIN
(
SELECT 1 AS a UNION ALL SELECT 2
) tbl2
fiddle
Possible to emulate a recursive CTE?
None of the solutions is very efficient, and most of them involve writing more code than you should. It would be better to upgrade to MySQL 8.0.
SQL works best on sets, not on iteration. So the standard way to generate a series in a single query is to already have a set of rows in a temporary table, and apply some set-based operations to it.
For example:
SELECT 0 AS num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5;
+-----+
| num |
+-----+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+-----+
With this as the basis, you can do date arithmetic and then extract the weekday names with DATE_FORMAT():
SELECT DATE_FORMAT(CURDATE() + INTERVAL num DAY, '%W') AS weekday
FROM (
SELECT 0 AS num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5
) AS t;
+-----------+
| weekday |
+-----------+
| Friday |
| Saturday |
| Sunday |
| Monday |
| Tuesday |
| Wednesday |
+-----------+
You could also prepare a fixed base table of integers, fill it with as many as you need, and use it for different purposes.
SELECT DATE_FORMAT(CURDATE() + INTERVAL num DAY, '%W') AS weekday
FROM MySetOfIntegers
WHERE num BETWEEN 0 AND 5;
The suggestion of using an iterative approach would involve writing a lot more code. It will also mean N SQL queries, each generating a separate result set, so that's more code you have to write in your application to fetch all the result sets and append them together.
You could write a recursive stored procedure, but there's a risk of exceeding the thread stack space if you allow deep recursion. The default limit on stored procedure recursion is 0. That is, no recursion is allowed at all unless you set a finite limit. See https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_max_sp_recursion_depth
Here's an example of a recursive stored procedure:
DROP PROCEDURE IF EXISTS Weekdays;
DELIMITER //
CREATE PROCEDURE Weekdays(IN date DATE, IN num INT)
BEGIN
IF num >= 1 THEN
CALL Weekdays(date, num-1);
END IF;
SELECT DATE_FORMAT(date + INTERVAL num-1 DAY, '%W') AS weekday;
END//
DELIMITER ;
And calling it. Note it produces multiple result sets.
mysql> set max_sp_recursion_depth=6;
mysql> call Weekdays(CURDATE(), 6);
+----------+
| weekday |
+----------+
| Thursday |
+----------+
1 row in set (0.00 sec)
+---------+
| weekday |
+---------+
| Friday |
+---------+
1 row in set (0.00 sec)
+----------+
| weekday |
+----------+
| Saturday |
+----------+
1 row in set (0.00 sec)
+---------+
| weekday |
+---------+
| Sunday |
+---------+
1 row in set (0.00 sec)
+---------+
| weekday |
+---------+
| Monday |
+---------+
1 row in set (0.00 sec)
+---------+
| weekday |
+---------+
| Tuesday |
+---------+
1 row in set (0.00 sec)
+-----------+
| weekday |
+-----------+
| Wednesday |
+-----------+
1 row in set (0.00 sec)
I may have gotten the recursion off by one somewhere. This actually supports the point that it's not as easy as it sounds to implement a recursive routine.
Oh and you get an error — and no results — if you exceed the recursion limit.
mysql> call Weekdays(CURDATE(), 8);
ERROR 1456 (HY000): Recursive limit 6 (as set by the max_sp_recursion_depth variable) was exceeded for routine Weekdays
In DBeaver, how can I run an SQL union query from two different connections..?
This is not a feature of DBeaver. DBeaver can only access the data that the DB gives it, and this is restricted to a single connection at a time (save for import/export operations). This feature is being considered for development, so keep an eye out for this answer to be outdated sometime in 2019.
You can export data from your OLD database and import it into ASP7 using DBeaver (although vendor tools for this are typically more efficient for this). Then you can do your union as suggested.
Many RDBMS offer a way to logically access foreign databases as if they were local, in which case DBeaver would then be able to access the data from the OLD database (as far as DBeaver is concerned in this situation, all the data is coming from a single connection). In Postgres, for example, one can use a foreign data wrapper to access foreign data.
I'm not familiar with DB2, but a quick Google search suggests that you can set up foreign connections within DB2 using nicknames or three-part-names.
How to emulate full outer join in this query?
You basically do a Union of LEFT and RIGHT JOIN.
You actually have interesting wrinkle in that you also want to limit the rows to 3. To solve that you need to
- Limit both The "left" and "right" selects by 3
- Then use the result of the UNION in a inline view
- then Limit the union by 3 again
UPDATE Sadly, unless I'm mistaken you can't do this directly in a UNION so you need to add another layer of inline views prior to the UNION
The LIMITS inside the UNION will offer some performance benefit and then the limit after will give you the correct results.
SELECT title,
teaser,
nid,
DATE,
image,
image_tid
FROM (SELECT title,
teaser,
nid,
DATE,
image,
image_tid,
created
FROM (SELECT DISTINCT n.title,
nr.teaser,
n.nid,
Date_format(From_unixtime(n.created),
'%M %e, %Y') AS
DATE,
f.filepath
AS
image,
tn_img.tid
AS
image_tid
,
n.created
FROM node n
JOIN node_revisions nr
ON n.nid = nr.nid
LEFT JOIN content_field_related_images cfri
ON ( n.nid = cfri.nid
AND cfri.delta = 0 )
LEFT JOIN content_field_att_file cfaf
ON cfri.field_related_images_nid = cfaf.nid
LEFT JOIN files f
ON cfaf.field_att_file_fid = f.fid
JOIN term_node tn2
ON n.nid = tn2.nid
LEFT OUTER JOIN term_node tn_img
ON cfri.field_related_images_nid = tn_img.nid
WHERE n.status = 1
AND n.TYPE = 'article'
AND nr.body LIKE '%kimberly-clark%'
AND tn2.tid = 143
ORDER BY n.created DESC
LIMIT 3) tleft
UNION
SELECT title,
teaser,
nid,
DATE,
image,
image_tid,
created
FROM (SELECT DISTINCT n.title,
nr.teaser,
n.nid,
Date_format(From_unixtime(n.created),
'%M %e, %Y') AS
DATE,
f.filepath
AS
image,
tn_img.tid
AS
image_tid
,
n.created
FROM node n
JOIN node_revisions nr
ON n.nid = nr.nid
LEFT JOIN content_field_related_images cfri
ON ( n.nid = cfri.nid
AND cfri.delta = 0 )
LEFT JOIN content_field_att_file cfaf
ON cfri.field_related_images_nid = cfaf.nid
LEFT JOIN files f
ON cfaf.field_att_file_fid = f.fid
JOIN term_node tn2
ON n.nid = tn2.nid
RIGHT OUTER JOIN term_node tn_img
ON cfri.field_related_images_nid = tn_img.nid
WHERE n.status = 1
AND n.TYPE = 'article'
AND nr.body LIKE '%kimberly-clark%'
AND tn2.tid = 143
ORDER BY n.created DESC
LIMIT 3) tright) t
ORDER BY created DESC
LIMIT 3
UPDATE
Using spencer7593 and ypercube suggestions here's an alternative approach using two UNION ALL statements and no inline views.
SELECT DISTINCT n.created,
n.title,
nr.teaser,
n.nid,
Date_format(From_unixtime(n.created), '%M %e, %Y') AS DATE,
f.filepath AS image,
tn_img.tid AS image_tid
FROM node n
JOIN node_revisions nr
ON n.nid = nr.nid
LEFT JOIN content_field_related_images cfri
ON ( n.nid = cfri.nid
AND cfri.delta = 0 )
LEFT JOIN content_field_att_file cfaf
ON cfri.field_related_images_nid = cfaf.nid
LEFT JOIN files f
ON cfaf.field_att_file_fid = f.fid
JOIN term_node tn2
ON n.nid = tn2.nid
LEFT OUTER JOIN term_node tn_img
ON cfri.field_related_images_nid = tn_img.nid
WHERE n.status = 1
AND n.TYPE = 'article'
AND nr.body LIKE '%kimberly-clark%'
AND tn2.tid = 143
UNION ALL
SELECT DISTINCT n.created,
n.title,
nr.teaser,
n.nid,
Date_format(From_unixtime(n.created), '%M %e, %Y') AS DATE,
f.filepath AS image,
tn_img.tid AS image_tid
FROM node n
JOIN node_revisions nr
ON n.nid = nr.nid
LEFT JOIN content_field_related_images cfri
ON ( n.nid = cfri.nid
AND cfri.delta = 0 )
LEFT JOIN content_field_att_file cfaf
ON cfri.field_related_images_nid = cfaf.nid
LEFT JOIN files f
ON cfaf.field_att_file_fid = f.fid
JOIN term_node tn2
ON n.nid = tn2.nid
RIGHT JOIN term_node tn_img
ON cfri.field_related_images_nid = tn_img.nid
WHERE n.status = 1
AND n.TYPE = 'article'
AND nr.body LIKE '%kimberly-clark%'
AND tn2.tid = 143
AND cfri.field_related_images_nid IS NULL
ORDER BY 1 DESC
LIMIT
3
Related Topics
How to Convert Spark Schemardd into Rdd of My Case Class
Lock Escalation - What's Happening Here
Is There a Difference Between a Select Statement Inside a Transaction and One That Is Outside of It
Fetch a Record on Maximum Date of Every Month
How to Use Explain Plan to Optimize Queries
Sp Taking 15 Minutes, But the Same Query When Executed Returns Results in 1-2 Minutes
SQL Queries on String Columns - Sorting According to Language
Delete the 'First' Record from a Table in SQL Server, Without a Where Condition
Simple Graph Search Algorithm in SQL (Postgresql)
Join One Row to Multiple Rows in Another Table
Convert Unknown Number of Comma Separated Varchars Within 1 Column into Multiple Columns
Index on Timestamp: Functions in Index Expression Must Be Marked as Immutable
SQL Server Output Parameter Issue
Add Missing Data from Previous Month or Year Cumulatively
What Is the Optimal Way to Compare Dates in Microsoft SQL Server