# Structure and Unions, User Defined Variables

19/03/2020

Similarities between Structure and Union

1. Both are user-defined data types used to store data of different types as a single unit.
2. Their members can be objects of any type, including other structures and unions or arrays. A member can also consist of a bit field.
3. Both structures and unions support only assignment = and sizeof operators. The two structures or unions in the assignment must have the same members and member types.
4. A structure or a union can be passed by value to functions and returned by value by functions. The argument must have the same type as the function parameter. A structure or union is passed by value just like a scalar variable as a corresponding parameter.
5. Operator is used for accessing members.

Structure and union both are user defined data types which contains variables of different data types. Both of them have same syntax for definition, declaration of variables and for accessing members. Still there are many difference between structure and union. In this tutorial we will take a look on those differences.

#### Difference between Structure and Union

Structure Union

In structure each member get separate space in memory. Take below example.

struct student { int rollno; char gender; float marks; }s1;

The total memory required to store a structure variable is equal to the sum of size of all the members. In above case 7 bytes (2+1+4) will be required to store structure variable s1.

In union, the total memory space allocated is equal to the member with largest size. All other members share the same memory space. This is the biggest difference between structure and union.

union student { int rollno; char gender; float marks; }s1;

In above example variable marks is of float type and have largest size (4 bytes). So the total memory required to store union variable s1 is 4 bytes.

We can access any member in any sequence.

s1.rollno = 20; s1.marks = 90.0; printf(“%d”,s1.rollno);

The above code will work fine but will show erroneous output in the case of union. We can access only that variable whose value is recently stored.

s1.rollno = 20; s1.marks = 90.0; printf(“%d”,s1.rollno);

The above code will show erroneous output. The value of rollno is lost as most recently we have stored value in marks. This is because all the members share same memory space.

All the members can be initialized while declaring the variable of structure. Only first member can be initialized while declaring the variable of union. In above example we can initialize only variable rollno at the time of declaration of variable.

User Defined Variables

User-defined variables are variables which can be created by the user and exist in the session. This means that no one can access user-defined variables that have been set by another user, and when the session is closed these variables expire. However, these variables can be shared between several queries and stored programs.

User-defined variables names must be preceded by a single at character (@). While it is safe to use a reserved word as a user-variable name, the only allowed characters are ASCII letters, digits, dollar sign (\$), underscore (_) and dot (.). If other characters are used, the name can be quoted in one of the following ways:

• @`var_name`
• @’var_name’
• @”var_name”

These characters can be escaped as usual.

User-variables names are case insensitive, though they were case sensitive in MySQL 4.1 and older versions.

User-defined variables cannot be declared. They can be read even if no value has been set yet; in that case, they are NULL. To set a value for a user-defined variable you can use:

SET statement;

:= operator within a SQL statement;

SELECT … INTO.

Since user-defined variables type cannot be declared, the only way to force their type is using CAST() or CONVERT():

SET @str = CAST(123 AS CHAR(5));

If a variable has not been used yet, its value is NULL:

SELECT @x IS NULL;

+————+

| @x IS NULL |

+————+

|          1 |

+————+

It is unsafe to read a user-defined variable and set its value in the same statement (unless the command is SET), because the order of these actions is undefined.

User-defined variables can be used in most MariaDB’s statements and clauses which accept an SQL expression. However there are some exceptions, like the LIMIT clause.

They must be used to PREPARE a prepared statement:

@sql = ‘DELETE FROM my_table WHERE c>1;’;

PREPARE stmt FROM @sql;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

Another common use is to include a counter in a query:

SET @var = 0;

SELECT a, b, c, (@var:=@var+1) AS counter FROM my_table;