What does the term "Tuple" Mean in Relational Databases?

Question

Please explain what is meant by tuples in sql?Thanks..

Solution 1

Most of the answers here are on the right track. However, a row is not a tuple. Tuples* are unordered sets of known values with names. Thus, the following tuples are the same thing (I'm using an imaginary tuple syntax since a relational tuple is largely a theoretical construct):

(x=1, y=2, z=3)
(z=3, y=2, x=1)
(y=2, z=3, x=1)

...assuming of course that x, y, and z are all integers. Also note that there is no such thing as a "duplicate" tuple. Thus, not only are the above equal, they're the same thing. Lastly, tuples can only contain known values (thus, no nulls).

A row** is an ordered set of known or unknown values with names (although they may be omitted). Therefore, the following comparisons return false in SQL:

(1, 2, 3) = (3, 2, 1)
(3, 1, 2) = (2, 1, 3)

Note that there are ways to "fake it" though. For example, consider this INSERT statement:

INSERT INTO point VALUES (1, 2, 3)

Assuming that x is first, y is second, and z is third, this query may be rewritten like this:

INSERT INTO point (x, y, z) VALUES (1, 2, 3)

Or this:

INSERT INTO point (y, z, x) VALUES (2, 3, 1)

...but all we're really doing is changing the ordering rather than removing it.

And also note that there may be unknown values as well. Thus, you may have rows with unknown values:

(1, 2, NULL) = (1, 2, NULL)

...but note that this comparison will always yield UNKNOWN. After all, how can you know whether two unknown values are equal?

And lastly, rows may be duplicated. In other words, (1, 2) and (1, 2) may compare to be equal, but that doesn't necessarily mean that they're the same thing.

If this is a subject that interests you, I'd highly recommend reading SQL and Relational Theory: How to Write Accurate SQL Code by CJ Date.

* Note that I'm talking about tuples as they exist in the relational model, which is a bit different from mathematics in general.

**And just in case you're wondering, just about everything in SQL is a row or table. Therefore, (1, 2) is a row, while VALUES (1, 2) is a table (with one row).

UPDATE: I've expanded a little bit on this answer in a blog post here.

Solution 2

It's a shortened "N-tuple" (like in quadruple, quintuple etc.)

It's a row of a rowset taken as a whole.

If you issue:

SELECT  col1, col2
FROM    mytable

, whole result will be a ROWSET, and each pair of col1, col2 will be a tuple.

Some databases can work with a tuple as a whole.

Like, you can do this:

SELECT  col1, col2
FROM    mytable
WHERE   (col1, col2) =
        (
        SELECT  col3, col4
        FROM    othertable
        )

, which checks that a whole tuple from one rowset matches a whole tuple from another rowset.

Solution 3

In relational databases, tables are relations (in mathematical meaning). Relations are sets of tuples. Thus table row in relational database is tuple in relation.

Wiki on relations:

In mathematics (more specifically, in set theory and logic), a relation is a property that assigns truth values to combinations (k-tuples) of k individuals. Typically, the property describes a possible connection between the components of a k-tuple. For a given set of k-tuples, a truth value is assigned to each k-tuple according to whether the property does or does not hold.

Solution 4

Whatever its use in mathematics, a tuple in RDBMS is commonly considered to be a row in a table or result set. In an RDBMS a tuple is unordered. A tuple in an MDDBMS is the instance of data in a cell with its associated dimension instances (members).

What is the tuple in a column family data store?

Solution 5

tuple = 1 record; n-tuple = ordered list of 'n' records; Elmasri Navathe book (page 198 3rd edition).

record = either ordered or unordered.

View more solutions

Comments

[flipdoubt]

Is it a row in a database table or any row in any set? If you create a projection from multiple tables, views, etc., is each row a tuple too?

[Tom Carter]

A tuple may be a sub set of the row

[Jason Baker]

@Tom Carter - A row may be a subset of the row as well. :-)

[John Saunders]

Jason, vartec said the opposite: table row in relational database is tuple in relation"

[Jason Baker]

He did also say "tables are relations (in mathematical meaning)." Perhaps I should have said "Tables aren't relations". :-)

[Lou]

Hello, I've read your answer and I've read similar answers on SO (Mike Samuel on this question and sampson-chen on that question) as well as here and wiki, and all say that tuples are ordered sets, not unordered. Your answer therefore confuses me.

[jwknz]

I am just going through this now, but if a row has a primary key which is unique, does that not make every row unique in that way? No matter how it is ordered?

[Rafael Eyng]

Basic question: (x=1, y=2, z=3) is 1 tuple or 3 tuples?

[Vincent Paing]

I don't get how (1, 2, 3) = (3, 2, 1) is not equal in SQL, a bad thing? I mean surely, they are two different things, assuming x = 3, y = 2, and z =1. I think the answer could be more clarified by not omitting name in your SQL example. I'm really confused even after reading your blog

[philipxy]

This answer fails to distinguish the different meanings of the term "tuple" by default in mathematics (ordered) vs in relational database contexts (unordered name-value pair sets). It would also help if you distinguished between expressions what they denote. Eg "1/2" & "3/6" are two different expresssions that denote the fractional value one half, ie 1/2. It would also help if you distinguished between equality of values and the SQL operator = that treats the value NULL specially. Eg per the previous distinction between expresssions & denotations, x = y doesn't mean x equals y.