> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Querying by Subnet in MySQL

> How to query rows by IP subnet in MySQL — bitwise range checks with INET_ATON, and cleaner subnet queries using VillageSQL's INET type and network functions.

> [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md)
>
> All examples on this page work on VillageSQL. Install Now →

Finding all rows where an IP address falls within a subnet requires comparing numeric IP values against a range. MySQL's standard approach uses bitwise arithmetic with `INET_ATON()`. VillageSQL's `INET` type provides network functions that make the same query more readable and work for both IPv4 and IPv6.

## The Standard Approach: Bitwise Range Checks

For IPv4, `INET_ATON()` converts an address to an unsigned 32-bit integer. A subnet membership check is a bitwise AND: if `(ip & netmask) == network_address`, the IP is in the subnet.

```sql theme={null}
CREATE TABLE access_log (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    ip_address INT UNSIGNED,
    accessed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO access_log (ip_address) VALUES
    (INET_ATON('192.168.1.10')),
    (INET_ATON('192.168.1.50')),
    (INET_ATON('192.168.2.1')),
    (INET_ATON('10.0.0.5'));

-- Find all IPs in 192.168.1.0/24
SELECT INET_NTOA(ip_address) FROM access_log
WHERE ip_address BETWEEN INET_ATON('192.168.1.0')
                     AND INET_ATON('192.168.1.255');

-- Or using bitwise AND with the netmask
SELECT INET_NTOA(ip_address) FROM access_log
WHERE (ip_address & 0xFFFFFF00) = INET_ATON('192.168.1.0');
```

Both work for IPv4. The `BETWEEN` approach is readable. The bitwise form is more composable when subnet parameters are variables.

Neither approach works for IPv6 — `INET_ATON()` only handles IPv4.

## With VillageSQL: Network Functions on INET Columns

VillageSQL's `vsql_network_address` extension provides containment predicates that answer "is this address inside that subnet" directly — `inet_contained_by()`, `inet_contains()`, and `inet_overlaps()` — plus `inet_network()`, `inet_set_masklen()`, and `inet_compare()` for building the check by hand. All of them work on both IPv4 and IPv6 `INET` columns.

```sql theme={null}
INSTALL EXTENSION vsql_network_address;
```

```sql theme={null}
CREATE TABLE access_log (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    ip_address INET,
    accessed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO access_log (ip_address) VALUES
    (inet_from_string('192.168.1.10')),
    (inet_from_string('192.168.1.50')),
    (inet_from_string('192.168.2.1')),
    (inet_from_string('10.0.0.5')),
    (inet_from_string('192.168.1.200'));
```

### Subnet containment query

To check whether a stored IP belongs to a subnet, mask the address to the subnet's prefix length and compare the result to the target network:

```sql theme={null}
-- All IPs in 192.168.1.0/24
SELECT inet_to_string(ip_address) FROM access_log
WHERE cidr_to_string(inet_network(inet_set_masklen(ip_address, 24)))
    = '192.168.1.0/24';
```

For a parameterized version where the subnet comes from a variable:

```sql theme={null}
SET @subnet     = '192.168.1.0/24';
SET @prefix_len = inet_masklen(inet_from_string(@subnet));
SET @network    = cidr_to_string(cidr_from_string(@subnet));

SELECT inet_to_string(ip_address) FROM access_log
WHERE cidr_to_string(inet_network(inet_set_masklen(ip_address, @prefix_len)))
    = @network;
```

### Containment predicates

The extension ships PostgreSQL's five containment predicates as functions. They
take two `INET` values and return `1`, `0`, or `NULL`:

| Function | PostgreSQL operator | True when |
| :- | :- | :- |
| `inet_contained_by(a, b)` | `a << b` | `a` is inside `b`, and `b` is the wider network |
| `inet_contained_by_or_equals(a, b)` | `a <<= b` | `a` is inside `b`, or the two networks are equal |
| `inet_contains(a, b)` | `a >> b` | `a` is the wider network and `b` is inside it |
| `inet_contains_or_equals(a, b)` | `a >>= b` | `b` is inside `a`, or the two networks are equal |
| `inet_overlaps(a, b)` | `a && b` | either network contains the other, or they are equal |

The subnet query above becomes a single call:

```sql theme={null}
-- All IPs in 192.168.1.0/24
SELECT inet_to_string(ip_address) FROM access_log
WHERE inet_contained_by(ip_address, inet_from_string('192.168.1.0/24'));
```

Only the network part of each value is compared, through the shorter of the two
prefix lengths — so `192.168.1.5/24` and `192.168.1.9/24` are the same network,
and the strict forms return `0` for them while the `_or_equals` forms return `1`.
Two addresses of different families never match: comparing an IPv4 value with an
IPv6 one returns `0` rather than raising an error, matching PostgreSQL.

Both arguments must be `INET`. A `CIDR` value is not coerced:

```text theme={null}
ERROR 3219 (HY000): Cannot initialize function 'inet_contains': argument 1 type mismatch (expected vsql_network_address.INET, got vsql_network_address.CIDR)
```

Pass `inet_from_string()` on the subnet instead, as the example above does. These
functions were added in `vsql_network_address` 0.0.5.

### Range-based containment with inet\_compare

An alternative that avoids recomputing the network on every row — useful when you have the broadcast address available:

```sql theme={null}
-- IPs in 192.168.1.0/24: from .0 to .255
SELECT inet_to_string(ip_address) FROM access_log
WHERE inet_compare(ip_address, inet_from_string('192.168.1.0'))   >= 0
  AND inet_compare(ip_address, inet_from_string('192.168.1.255')) <= 0;
```

### IPv6 subnet queries (same pattern)

The same `inet_network()` approach works for IPv6 without any changes:

```sql theme={null}
CREATE TABLE ipv6_log (id INT, ip INET);
INSERT INTO ipv6_log VALUES
    (1, inet_from_string('2001:db8::1')),
    (2, inet_from_string('2001:db8::100')),
    (3, inet_from_string('2001:db9::1'));

-- Find all IPs in 2001:db8::/32
SET @v6_prefix = 32;
SET @v6_network = '2001:0db8:0000:0000:0000:0000:0000:0000/32';

SELECT inet_to_string(ip) FROM ipv6_log
WHERE cidr_to_string(inet_network(inet_set_masklen(ip, @v6_prefix)))
    = @v6_network;
```

### Networks table pattern

A common use case: a `networks` table with authorized CIDRs, and checking whether an incoming IP is in any of them.

```sql theme={null}
CREATE TABLE networks (
    id      INT AUTO_INCREMENT PRIMARY KEY,
    network CIDR,
    label   VARCHAR(100)
);

INSERT INTO networks (network, label) VALUES
    (cidr_from_string('192.168.1.0/24'), 'office-lan'),
    (cidr_from_string('10.0.0.0/8'),     'vpn-range'),
    (cidr_from_string('172.16.0.0/12'),  'docker-range');

-- Is an incoming IP in any authorized network?
-- inet_masklen() takes INET, not CIDR — convert via string to get the prefix length
SELECT label FROM networks
WHERE cidr_to_string(inet_network(inet_set_masklen(
    inet_from_string('192.168.1.42'),
    inet_masklen(inet_from_string(cidr_to_string(network)))
))) = cidr_to_string(network);
```

## Standard vs VillageSQL

| Approach | IPv6 | Readable | No manual calculation |
| :- | :- | :- | :- |
| `BETWEEN INET_ATON()` | No | Moderate | Requires computing broadcast manually |
| Bitwise AND with `INET_ATON()` | No | Low | Requires computing netmask manually |
| `inet_network()` + `inet_set_masklen()` | Yes | High | Functions handle the math |
| `inet_contained_by()` | Yes | High | One call, no manual calculation |

## Frequently Asked Questions

#### Does VillageSQL have PostgreSQL's `<<` containment operator?

The behavior is there, as functions rather than operators: `inet_contained_by()` for `<<`, `inet_contains()` for `>>`, the `_or_equals` variants for `<<=` and `>>=`, and `inet_overlaps()` for `&&`. See [Containment predicates](#containment-predicates).

#### Does the subnet query use the index?

The `cidr_to_string(inet_network(inet_set_masklen(...)))` pattern does not use an index scan — it recomputes the network address for every row. For large tables, the `inet_compare` range approach is more index-friendly since it expresses the query as a range on the `ip_address` column.

#### Can I store subnets and query containment at the same time?

Yes. Store both the host address and the subnet in `INET` columns and compare them with `inet_contained_by(host_ip, subnet)`. If the subnet is in a `CIDR` column, convert it first — the containment predicates take `INET` on both sides and do not coerce `CIDR`.

## Troubleshooting

| Problem | Solution |
| :- | :- |
| `FUNCTION <database>.inet_network does not exist` | Run `INSTALL EXTENSION vsql_network_address` |
| Subnet query returns no rows | Verify `@network` matches the expanded CIDR string — IPv6 CIDRs use expanded notation |
| Range query includes addresses outside the subnet | Check the broadcast address; for /24 the range is `.0` to `.255` |
| Mixed IPv4/IPv6 column returns wrong results | IPv4 and IPv6 addresses don't overlap — the query returns correct results per-family |

## See also

* [Storing IP Addresses in MySQL](https://villagesql.com/docs/en/guides/storing-ip-addresses.md) — the INET type used in subnet queries
* [IPv6 Storage in MySQL](https://villagesql.com/docs/en/guides/ipv6-storage.md) — subnet queries work the same for IPv6 with INET
* [Geolocation Lookups by IP in MySQL](https://villagesql.com/docs/en/guides/ip-geolocation.md) — going further with IP data beyond subnets


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.